Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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.

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    Limited to secondary for now. the one in the 2nd table can be said as
    unique records

    If so, this can be done easily by

    select min(id) as id,
       f_name,
       l_name,
       phone,
       case when max(id) = min(id) then null else max(id) end as seconday_id
from mytable
group by f_name,l_name,phone;

    See example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search