How to select non-distinct rows with a distinct on multiple columns

Question

I have found many answers on selecting non-distinct rows where they group by a singular column, for example, e-mail. However, there seems to have been issue in our system where we are getting some duplicate data whereby everything is the same except the identity column.

SELECT DISTINCT 
      COLUMN1,
      COLUMN2,
      COLUMN3,
      ...
      COLUMN14
  FROM TABLE1

How can I get the non-distinct rows from the query above? Ideally it would include the identity column as currently that is obviously missing from the distinct query.

Can you provide sample data for this? I'm having a hard time understanding what your expected result is. — Siyual
– Siyual, Commented Sep 18, 2015 at 12:23
It's basically people's contact details, where there are double entries for some people and the only difference is the ID column — ediblecode
– ediblecode, Commented Sep 18, 2015 at 12:26

ediblecode · Accepted Answer · 2015-09-18 13:05:21Z

14

select COLUMN1,COLUMN2,COLUMN3 
from TABLE_NAME 
group by COLUMN1,COLUMN2,COLUMN3 
having COUNT(*) > 1

edited Sep 18, 2015 at 13:05

ediblecode

12k21 gold badges72 silver badges118 bronze badges

answered Sep 18, 2015 at 12:38

kanishka

1661 silver badge10 bronze badges

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

1 Comment

kanishka Over a year ago

also dont select the id column as it would be always distinct .

Rachel Ambler · Accepted Answer · 2015-09-18 13:02:38Z

1

With _cte (col1, col2, col3, id) As
    (
        Select cOl1, col2, col3, Count(*)
          From mySchema.myTable
          Group By Col1, Col2, Col3
          Having Count(*) > 1
    )
Select t.*
From _Cte As c
Join mySchema.myTable As t
On c.col1 = t.col1
And c.col2 = t.col2
And c.col3 = t.col3

answered Sep 18, 2015 at 13:02

Rachel Ambler

1,61413 silver badges28 bronze badges

1 Comment

AdamKalisz Over a year ago

The descriptions are not very descriptive but it helped me. Basically first it does the aggregation to find duplicates. Then it finds whole rows from the table based on a comparison of multiple columns from the aggregation (the inner join part) and gives out just the columns of the original table.

Flair · Accepted Answer · 2021-10-05 09:32:04Z

1

SELECT * FROM 
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY COL 1, COL 2, .... COL N ORDER BY COL M
) RN
FROM TABLE_NAME 
)T
 WHERE T.RN>1

edited Oct 5, 2021 at 9:32

Flair

2,9572 gold badges33 silver badges45 bronze badges

answered Oct 4, 2021 at 18:03

Mathew Philip

111 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

How to select non-distinct rows with a distinct on multiple columns

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related