Select the duplicate rows with specific values

Question

How can I only get the data with the same ID, but not the same Name?

The following is the example to explain my thought. Thanks.

ID Name Date 123 Amy 08/03/2022 123 Amy 12/03/2022 456 Billy 08/03/2022 456 Cat 09/03/2022 789 Peter 10/03/2022

Expected Output:

ID Name Date 456 Billy 08/03/2022 456 Cat 09/03/2022

How I have done.

select ID, Name, count(*) from table groupby ID, Name having count(*) > 1

But the result included the following parts that I do not want it.

ID Name Date 123 Amy 08/03/2022 123 Amy 12/03/2022

While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Mar 7, 2022 at 15:22

Derrick Moeller · Accepted Answer · 2022-03-07 15:19:59Z

One approach would be to use a subquery to identify IDs that have multiple names.

SELECT * FROM YourTable WHERE ID IN (SELECT ID FROM YourTable GROUP BY ID HAVING COUNT(DISTINCT Name) > 1)

NTDLS · Accepted Answer · 2022-03-07 15:38:28Z

0

I'd join the table to its self like this:

SELECT DISTINCT a.Id as ID_A, b.Id as ID_B, a.[Name] as Name_A FROM Test as a INNER JOIN Test as b ON A.Id = B.Id WHERE A.[Name] <> B.[Name]

edited Mar 7, 2022 at 15:38

answered Mar 7, 2022 at 15:24

NTDLS

4,8804 gold badges47 silver badges71 bronze badges

3 Comments

jarlh Over a year ago

What will happen if there's a 3rd row with id = 456?

NTDLS Over a year ago

@jarlh you'd probably want to get the distinct to filter those down.

Derrick Moeller Over a year ago

DISTINCT doesn't change this query, you'll still end up with every combination or n * (n-1) rows.

score 0 · Accepted Answer · 2022-03-07 16:08:09Z

0

Do you want

SELECT * FROM table_name WHERE ID = 456;

or

SELECT * FROM table_name WHERE ID IN (SELECT ID FROM table_name GROUP BY ID HAVING COUNT(DISTINCT name) > 1 );

?

edited Mar 7, 2022 at 16:08

answered Mar 7, 2022 at 15:21

user18098820

2 Comments

jarlh Over a year ago

Your subquery should only return the id column. (As it is now, you'll get an error.)

jarlh Over a year ago

And the HAVING clause condition should be > 1.

Charlieface · Accepted Answer · 2022-03-07 17:10:38Z

Window functions are likely to be the most efficient here. They do not require self-joining of the source table.

Unfortunately, SQL Server does not support COUNT(DISTINCT as a window function. But we can simulate it by using DENSE_RANK and MAX

WITH DistinctRanks AS ( SELECT *, rnk = DENSE_RANK(*) OVER (PARTITION BY ID ORDER BY Name) FROM YourTable ), MaxRanks AS ( SELECT *, mr = MAX(rnk) OVER (PARTITION BY ID) FROM DistinctRanks ) SELECT ID, Name, Count FROM MaxRanks t WHERE t.mr > 1;

This query identifies IDs that exist more than once, it doesn't take into account the OPs name requirement. Unfortunately it can't be fixed using DISTINCT, you can use DENSE_RANK though. stackoverflow.com/a/22347502/3194005
Righto, missed that, have modified. Hey that's funny, you edited your comment the exact same time I changed the query, although I had a different solution

Collectives™ on Stack Overflow

Select the duplicate rows with specific values

4 Answers 4

Comments

3 Comments

2 Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

2 Comments

Linked

Related