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

3

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)

answered Mar 7, 2022 at 15:19

Derrick Moeller

5,0502 gold badges27 silver badges49 bronze badges

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

Comments

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

0

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;

edited Mar 7, 2022 at 17:10

answered Mar 7, 2022 at 16:32

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

2 Comments

Derrick Moeller Over a year ago

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

Charlieface Over a year ago

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

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related