Select all records where last n characters in column are not unique

Question

I have bit strange requirement in mysql. I should select all records from table where last 6 characters are not unique.

for example if I have table:

I should select row 1 and 3 since last 6 letters of this values are not unique.

Do you have any idea how to implement this? Thank you for help.

Answer 1

Something like that should work:

SELECT `mytable`.*
FROM (SELECT RIGHT(`value`, 6) AS `ending` FROM `mytable` GROUP BY `ending` HAVING COUNT(*) > 1) `grouped`
INNER JOIN `mytable` ON `grouped`.`ending` = RIGHT(`value`, 6)

but it is not fast. This requires a full table scan. Maybe you should rethink your problem.

Answer 2

EDITED: I had a wrong understanding of the question previously and I don't really want to change anything from my initial answer. But if my previous answer is not acceptable in some environment and it might mislead people, I have to correct it anyhow.

SELECT GROUP_CONCAT(id),RIGHT(VALUE,6)
FROM table1
GROUP BY RIGHT(VALUE,6) HAVING COUNT(RIGHT(VALUE,6)) > 1;

Since this question already have good answers, I made my query in a slightly different way. And I've tested with sql_mode=ONLY_FULL_GROUP_BY. ;)

Answer 3

I uses a JOIN against a subquery where I count the occurences of each unique combo of n (2 in my example) last chars

SELECT t.*
FROM t
JOIN (SELECT RIGHT(value, 2) r, COUNT(RIGHT(value, 2)) rc 
      FROM t 
      GROUP BY r) c ON c.r = RIGHT(value, 2) AND c.rc > 1

Answer 4

This is what you need: a subquery to get the duplicated right(value,6) and the main query yo get the rows according that condition.

SELECT t.* FROM t WHERE RIGHT(`value`,6) IN (
    SELECT RIGHT(`value`,6)
    FROM t
    GROUP BY RIGHT(`value`,6) HAVING COUNT(*) > 1);

UPDATE

This is the solution to avoid the mysql error in the case you have sql_mode=only_full_group_by

SELECT t.* FROM t WHERE RIGHT(`value`,6) IN (
    SELECT DISTINCT right_value FROM (
        SELECT RIGHT(`value`,6) AS right_value, 
               COUNT(*) AS TOT
        FROM t
        GROUP BY RIGHT(`value`,6) HAVING COUNT(*) > 1)  t2
        ) 

Fiddle here

Answer 5

Might be a fast code, as there is no counting involved.

Live test: https://www.db-fiddle.com/f/dBdH9tZd4W6Eac1TCRXZ8U/0

select *
from tbl outr
where not exists
(
    select 1 / 0 -- just a proof that this is not evaluated. won't cause division by zero
    from tbl inr
    where 
        inr.id <> outr.id
        and right(inr.value, 6) = right(outr.value, 6)  
)

Output:

| id  | value           |
| --- | --------------- |
| 2   | aaaaaaaaaaaaaa  |
| 4   | aaaaaaaaaaaaaaB |
| 5   | Hello           |

The logic is to test other rows that is not equal to the same id of the outer row. If those other rows has same right 6 characters as the outer row, then don't show that outer row.

UPDATE

I misunderstood the OP's intent. It's the reversed. Anyway, just reverse the logic. Use EXISTS instead of NOT EXISTS

Live test: https://www.db-fiddle.com/f/dBdH9tZd4W6Eac1TCRXZ8U/3

select *
from tbl outr
where exists
(
    select 1 / 0 -- just a proof that this is not evaluated. won't cause division by zero
    from tbl inr
    where 
        inr.id <> outr.id
        and right(inr.value, 6) = right(outr.value, 6)  
)

Output:

| id  | value       |
| --- | ----------- |
| 1   | abcdePuzzle |
| 3   | abcPuzzle   |

UPDATE

Tested the query. The performance of my answer (correlated EXISTS approach) is not optimal. Just keeping my answer, so others will know what approach to avoid :)

GhostGambler's answer is faster than correlated EXISTS approach. For 5 million rows, his answer takes 2.762 seconds only:

explain analyze                                   
SELECT
    tbl.*
FROM
    (
        SELECT
            RIGHT(value, 6) AS ending
        FROM
            tbl
        GROUP BY
            ending
        HAVING
            COUNT(*) > 1
    ) grouped
    JOIN tbl ON grouped.ending = RIGHT(value, 6)                                                

My answer (correlated EXISTS) takes 4.08 seconds:

explain analyze
select *
from tbl outr
where exists
(
    select 1 / 0 -- just a proof that this is not evaluated. won't cause division by zero
    from tbl inr
    where 
        inr.id <> outr.id
        and right(inr.value, 6) = right(outr.value, 6)          
)

Straightforward query is the fastest, no join, just plain IN query. 2.722 seconds. It has practically the same performance as JOIN approach since they have the same execution plan. This is kiks73's answer. I just don't know why he made his second answer unnecessarily complicated.

So it's just a matter of taste, or choosing which code is more readable select from in vs select from join

explain analyze
SELECT *
FROM tbl
where right(value, 6) in 
    (
        SELECT
            RIGHT(value, 6) AS ending
        FROM
            tbl
        GROUP BY
            ending
        HAVING
            COUNT(*) > 1
    ) 

Result:

---

Test data used:

CREATE TABLE tbl (
  id INTEGER primary key,
  value VARCHAR(20)
);

INSERT INTO tbl
  (id, value)
VALUES
  ('1', 'abcdePuzzle'),
  ('2', 'aaaaaaaaaaaaaa'),
  ('3', 'abcPuzzle'),
  ('4', 'aaaaaaaaaaaaaaB'),
  ('5', 'Hello');


insert into tbl(id, value)
select x.y, 'Puzzle'
from generate_series(6, 5000000) as x(y);

create index ix_tbl__right on tbl(right(value, 6));

---

Performances without the index, and with index on tbl(right(value, 6)):

JOIN approach:

Without index: 3.805 seconds

With index: 2.762 seconds

IN approach:

Without index: 3.719 seconds

With index: 2.722 seconds

Answer 6

Just a bit neater code (if using MySQL 8.0). Can't guarantee the performance though

Live test: https://www.db-fiddle.com/f/dBdH9tZd4W6Eac1TCRXZ8U/1

select x.*
from 
(
    select  
        *, 
        count(*) over(partition by right(value, 6)) as unique_count
    from tbl
 ) as x
 where x.unique_count = 1                 

Output:

| id  | value           | unique_count |
| --- | --------------- | ------------ |
| 2   | aaaaaaaaaaaaaa  | 1            |
| 4   | aaaaaaaaaaaaaaB | 1            |
| 5   | Hello           | 1            |

UPDATE

I misunderstood OP's intent. It's the reversed. Just change the count:

select x.*
from 
(
    select  
        *, 
        count(*) over(partition by right(value, 6)) as unique_count
    from tbl
 ) as x
 where x.unique_count > 1                 

Output:

| id  | value       | unique_count |
| --- | ----------- | ------------ |
| 1   | abcdePuzzle | 2            |
| 3   | abcPuzzle   | 2            |