Search code examples
mysqljoin

sql query to get the unique column value for duplicate rows as a separate column

lets get to the situation quickly,

so i have a table which contains values as

id f_name l_name phone
1 rohan mehra 9090909090
2 yusuf yohana 1010101010
3 rohan mehra 9090909090
4 yusuf yohana 1010101010

so if yu observe, there are duplicates which i want to extract. what i exactly want is to create a new table with same columns and contains values :-

id f_name l_name phone secondary_id
1 rohan mehra 9090909090 3
2 yusuf yohana 1010101010 4

can you help me obtain this. thanks in advance.

Solution

  • You could do this with a self-join, including the unique columns in the join criteria:

    SELECT t1.id, t1.f_name, t1.l_name, t1.phone, t2.id AS secondary_id
FROM mytable t1
INNER JOIN mytable t2 ON t2.id > t1.id
  AND t1.f_name = t2.f_name
  AND t1.l_name = t2.l_name
  AND t1.phone = t2.phone