How to display duplicates in SQL only if another column is different?

Question

So say I have this table:

Name	Role
First	Science
First	Math
First	Science
First	Math
Second	Science
Third	Math
Third	Math

I want to display a column of duplicates for Name/Role ONLY if role is different in each group. So the final result should be like this:

Name	Role
First	Science
First	Math

This is the only person that has a different role for the same name (no matter how many times that specific combination is duplicated). That's why even though Third/Math is also duplicated, it doesn't matter because it's the same combination.

I tried doing a CTE as follows:

;with cte as (
Select Name, Role, ROW_NUMBER() over (partition by name order by name) as 'rownum1' 
from U.Users
group by u.name, u.role)

so then select * from cte where rownum > 1 gets me my names of people that have this issue but it doesn't display the duplicate roles for that user. Not sure how I should approach it differently?

If I join the CTE table to the original Users table, I also get the single entries.

What DBMS is this (MySQL. T-SQL, PostgreSQL, SQLite, etc.)? Please update your tags accordingly. — Jesse
– Jesse, Commented Jun 23, 2022 at 21:27

Kurt · Accepted Answer · 2022-06-23 22:50:16Z

2

You can take advantage of the fact that window functions are applied after aggregation:

select name, role
from (
  select name, role, count(1) over (partition by name) c
  from user_role
  group by name, role
) r
where c > 1

https://www.db-fiddle.com/f/vzRDgBXwYp3VpgNyfn9qzL/0

answered Jun 23, 2022 at 22:50

Kurt

1,7481 gold badge5 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2532928 Over a year ago

I liked this solution and it worked! no complicated joins - but what is the count(1) over partition by name doing, basically counting the sets of usernames in the sub query?

Kurt Over a year ago

it's using count as a window function instead of an aggregate function. after the GROUP BY is processed, you're left with the unique name/role combinations. count(1) over (partition by name) is counting for each row how many rows exist with that name. 'First' appears twice so both of its rows have a count of 2, which we can then filter in the outer query

Jocohan · Accepted Answer · 2022-06-23 22:30:54Z

0

You can try something like this:

WITH cte1 as (
SELECT distinct *
FROM 
table1
),
cte2 as 
(
  Select Name, Role, ROW_NUMBER() over (partition by name order by name) as rnk 
  from cte1 u 
  group by u.name, u.role
 )
      
SELECT * FROM cte2
where name in
(select name
from cte2
WHERE rnk > 1
group by name
)

I used a distinct function to remove any duplicates, then use the ROW_NUMBER() like you to find Names with multiple rows.

db fiddle link

answered Jun 23, 2022 at 22:30

Jocohan

3844 silver badges6 bronze badges

Comments

user2532928 · Accepted Answer · 2022-06-24 00:32:59Z

0

So after I posted question I tried this which isn't as elegant as Kurt's answer but did also work:

;with cte as (select name, role, row_number() over (partition by name order by name) rownum

from user_role 
group by name, role)
          
          select distinct user_role.name, user_role.role from user_role 
          join  cte on cte.name=user_role.name and cte.role=user_role.role
          where user_role.name in (select name from cte where rownum =2)

https://www.db-fiddle.com/f/vzRDgBXwYp3VpgNyfn9qzL/2

answered Jun 24, 2022 at 0:32

user2532928

699 bronze badges

Collectives™ on Stack Overflow

How to display duplicates in SQL only if another column is different?

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related