Select Query to find Duplicated Values from the table

Question

How to get the duplicated information from the table like

Docnum |CustomerName| DocumentValue| Date
101    |ABC         | 10           | 14-04-18
102    |ABC         | 10           | 14-04-18
103    |LMN         | 11           | 14-04-18
104    |KFB         | 11           | 15-04-18
105    |KFB         | 12           | 16-04-18
106    |KFB         | 12           | 16-04-18
107    |KFB         | 12           | 17-04-18
108    |XYZ         | 12           | 17-04-18

Result should be:

Docnum |CustomerName| DocumentValue| Date     | Count
101    |ABC         | 10           | 14-04-18 | 2
102    |ABC         | 10           | 14-04-18 | 2
105    |KFB         | 12           | 16-04-18 | 3
106    |KFB         | 12           | 16-04-18 | 3
107    |KFB         | 12           | 16-04-18 | 3

I want to answer, but I know this is a duplicate of something, which you yourself could have found with 5-10 minutes of research, so I won't answer. — Tim Biegeleisen, Apr 19 '18 at 15:13
https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — Jacob H, Apr 19 '18 at 15:13
These are like some of the most popular posts on this site... a simple search would have got you the answer easily. — Jacob H, Apr 19 '18 at 15:13

hlagos · Accepted Answer · 2018-04-19T15:27:37.970

0

you can use window function if you want to find duplicates based on customer, documentvalue and date and keep the docnum.

SELECT Docnum , CustomerName, DocumentValue, Date,  c from
(
SELECT Docnum , CustomerName, DocumentValue, Date, COUNT(1) OVER(PARTITION BY CustomerName, DocumentValue, Date) AS c  

) t where c >= 1;

edited Apr 19 '18 at 15:27

answered Apr 19 '18 at 15:15

hlagos

7,690
3
23
41

2

Looking at the sample data this will return exactly zero rows because Docnum appear to be a sequential number. – Sean Lange Apr 19 '18 at 15:16
thanks, just updated – hlagos Apr 19 '18 at 15:28
@SeanLange you are right. the output is null. – Naveen.A Apr 19 '18 at 15:48
@hlagos Many Thanks. – Naveen.A Apr 19 '18 at 15:55

Select Query to find Duplicated Values from the table

1 Answers1