Mr x Mr x - 7 months ago 10
SQL Question

Select similar records from mysql

I have a database as shown below

ID color number code
102966 red 1 9f6606069f9b999b
102968 red 1 9f6606069f9b999b
102967 red 1 9f0606069f9f9f9f
102969 red 1 9f0606069f9f9f9f
103630 red 1 bbff9f0f8fdc9f7e
101582 red 1 bbff9b0fcf9f99d9
102000 red 1 99fd9f0fab999fff
101603 red 1 bbff9f0d8f9d96df
102016 red 1 bbff9900c09999df


This table has more then 4000 entries.
I got this output by using

Select * from mytable order by code asc


Now I want output as a Id-Id-Id.... where code is similar.
So for given snippet it should come like

102966-102968
102967-102969


So I want like that those records are similar, their code should come like this.
Please help.

Answer

I don't really see what your last result row is about, it might be a mistake or I don't understand what you need

I think you are looking for a group_concat

select group_concat(ID ORDER BY ID SEPARATOR '-' ) AS dup
from mytable
GROUP BY code
HAvING count(*) >1
ORDER BY dup    

Results:

|           dup |
|---------------|
| 102966-102968 |
| 102967-102969 |

A group_concat shows all the values matching the GROUP BY, here the ID values. The term SEPARATOR is there to specify - as separator between your IDs, because the default separator is ,

If you want all rows, even those for which there are no duplicate code, remove the having clause

SQL Fiddle

Comments