skip to Main Content
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.

2

Answers


  1. 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. 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