CODEWITHSUNDEEP
mysqlsqlmariadb
MTiff112
MTiff112

Reputation: 13

How to move ID of duplicate names to a separate column

I have a MySQL table with two duplicate names, how can I separate the IDs of the duplicate names into a separate column?

   ---------------------------------
   | id   | name       | surname   |
   ---------------------------------     
   |  557 | Logan      | Smith     |
   | 1052 | Logan      | Smith     |
   ---------------------------------

For example, like this:

   ----------------------------------------
   | id   |  id2  | name      | surname   |
   ----------------------------------------     
   | 557  |  1052 | Logan     | Smith     |
   ----------------------------------------

This is my current SQL statement to retrieve the current results in the first table:

SELECT userid as id, name, surname
FROM user
WHERE name = "Logan"
GROUP BY userid

There is only one duplicate at most for each name.

Thanks

Upvotes: 1

Views: 75

Answers (2)

Ian Kenney
Ian Kenney

Reputation: 6426

You can use something like:

with cte as (
   select 
       row_number() over (partition by name, surname order by user_id)  rn, 
       name,
       surname,
       user_id
    from tt
)
select 
   name,
   surname,
  max(case when rn=1 then user_id end) as id_1, 
  max(case when rn=2 then user_id end) as id_2 
from cte 
group by name, surname

Upvotes: 0

James
James

Reputation: 3015

If you are sure that the maximum is always 2... then you could:

SELECT min(userid) as id1, max(userid) as id2, name, surname
FROM user
WHERE name = "Logan"
GROUP BY name, surname

If you want to sofisticate a little bit more the query

SELECT min(userid) as id1,
  case when min(userId) = max(userid) then null else max(userId) end as id2, name, surname
FROM user
WHERE name = "Logan"
GROUP BY name, surname

Upvotes: 1

Related Questions