MySQL find pairs in the same column

user_9016889 0
Viewed 33

I have a table that looks something like this:

id id_dlry
0 12345.67
1 12345-68
2 12345
3 12345-67

I need to find pairs in column id_dlry where id's numbers are the same, but the sign changes from '.' to '-'

I added another temporary column to the table, where I can identify the sign like this:

update products  set check_sign =
                (case
                when id_dlry REGEXP '[0-9]{5}[.][A-Z0-9]+' THEN '1'
                when id_dlry REGEXP '[0-9]{5}[-][A-Z0-9]+' THEN '2'
                else '0'
                end);

so it looks like this:

id id_dlry check_sign
0 12345.67 1
1 12345-68 2
2 12345 0
3 12345-67 2

Now I would like to create another table only with pairs of records like "12345.67" and "12345-67", so it might look like this:

id id_dlry check_sign
0 12345.67 1
3 12345-67 2

I tried using inner join but I got stuck.

mysql
1 Answers

You don't need the new column. Use count(*) over(partition by replace(id_dlry, '.', '-')):

select id, id_dlry
from
(select id, 
id_dlry,
count(*) over(partition by replace(id_dlry, '.', '-')) cnt
from products) t
where cnt > 1;

Fiddle

user_176450990
Related