How to find duplicate data in a table

Question

I am trying to find duplicate data across 3 columns in the same table. I am looking for any orders which have the same ReferenceNumber, PremiseId & DatePlaced.

For example an order with ReferenceNumber of 12345 from PremiseId 67890 with DatePlaced 02/08/17 would show up, because all three criteria are satisfied.

This is what I have so far, which was taken from another answer on this site.

SELECT ReferenceNumber, PremiseId, DatePlaced
FROM [ypolive_Integration].[dbo].[OrderHeaders]
WHERE ReferenceNumber in
(SELECT ReferenceNumber FROM [ypolive_Integration].[dbo].[OrderHeaders]
GROUP BY ReferenceNumber
HAVING COUNT(*)>1)

Order By DatePlaced desc

This is returning results for ReferenceNumber, PremiseId & DatePlaced. However this is showing all data and I only want it to display the duplicates which match all three criteria.

I am very much a novice at this sort of thing, so apologies if this is completely wrong!

This is now solved with the below code:

select ReferenceNumber, PremiseId , CONVERT(VARCHAR(10),DatePlaced, 112) as 
DatePlaced,count(1) as cnt 
from [ypolive_Integration].[dbo].[OrderHeaders]
group by ReferenceNumber, PremiseId , CONVERT(VARCHAR(10),DatePlaced, 112)
having count(1) > 1 
order by DatePlaced desc

Are you using MySQL or MS SQL Server? Don't tag products not involved. — jarlh, Aug 03 '17 at 09:42
you could refer this link to get some idea https://stackoverflow.com/a/2594855/7083586 — Zaza, Aug 03 '17 at 09:44
update your question add a proper data sample and the expected result — ScaisEdge, Aug 03 '17 at 09:45

Kannan Kandasamy · Answer 1 · 2017-08-03T09:49:47.820

1

You can query as below:

Select * from (
    SELECT --ReferenceNumber, PremiseId, DatePlaced, 
            *
           ,RowN = Row_Number() over (partition by ReferenceNumber, PremiseId order by DatePlaced desc)
    FROM [ypolive_Integration].[dbo].[OrderHeaders]
) a
Where a.RowN > 1

edited Aug 03 '17 at 09:49

answered Aug 03 '17 at 09:44

Kannan Kandasamy

13,405
3
25
38

Hi, thank you for your reply. This returns the following code: The column 'ReferenceNumber' was specified multiple times for 'a'. – Chris W Aug 03 '17 at 09:47

Aparna · Accepted Answer · 2017-08-03T10:05:44.363

1

Please try the below query

 select ReferenceNumber, PremiseId , CONVERT(VARCHAR(10),DatePlaced, 112) as 
 DatePlaced,count(1) as cnt 
 from [ypolive_Integration].[dbo].[OrderHeaders]
 group by ReferenceNumber, PremiseId , CONVERT(VARCHAR(10),DatePlaced, 112)
 having count(1) > 1

edited Aug 03 '17 at 10:05

answered Aug 03 '17 at 09:48

Aparna

286
1
11

Hi, the code when ran provided the following error: Msg 402, Level 16, State 1, Line 3 The data types int and datetime are incompatible in the '&' operator. – Chris W Aug 03 '17 at 09:50
I believe this might work, however as the DatePlaced field is formatted as YYYY-MM-DD HH-MM-SS I think the query will not find duplicates that are not to the exact second. I need to find a way to just search on the Date rather than the Time. – Chris W Aug 03 '17 at 09:59
Have edited the code.This will extract the year,month and date and will compare – Aparna Aug 03 '17 at 10:06
I am just curious why do you convert it into [varchar] instead the [date] itself? – Bartosz X Aug 03 '17 at 10:13
I am just trying to extract the Year,Month and Date from the Date column .we have a datepart function to extract say a particular day or year.But together if you want this is the option take the first 10 and then convert it to varchar .Since this is to just find the duplicates this will suffice – Aparna Aug 03 '17 at 10:25

Shane · Answer 3 · 2017-08-03T10:33:44.693

SELECT ReferenceNumber
    ,PremiseId
    ,DatePlaced
    ,convert(VARCHAR(100), ReferenceNumber) + ' - ' + convert(VARCHAR(100), PremiseId) + ' - ' + convert(VARCHAR(100), DatePlaced, 121) combined  --Remove this line if not needed
FROM [ypolive_Integration].[dbo].[OrderHeaders]
WHERE convert(VARCHAR(100), ReferenceNumber) + ' - ' + convert(VARCHAR(100), PremiseId) + ' - ' + convert(VARCHAR(100), DatePlaced, 121) IN (
        SELECT convert(VARCHAR(100), ReferenceNumber) + ' - ' + convert(VARCHAR(100), PremiseId) + ' - ' + convert(VARCHAR(100), DatePlaced, 121) combined
        GROUP BY convert(VARCHAR(100), ReferenceNumber) + ' - ' + convert(VARCHAR(100), PremiseId) + ' - ' + convert(VARCHAR(100), DatePlaced, 121)
        HAVING COUNT(*) > 1
        )
ORDER BY DatePlaced DESC

Just change the "convert (varchar(100) " lengths and remove first combined statement as is needed — Shane, Aug 03 '17 at 10:35

score -1 · Answer 4 · answered Aug 03 '17 at 09:48

-1

Try using this query :

SELECT ReferenceNumber, PremiseId, DatePlaced
FROM [ypolive_Integration].[dbo].[OrderHeaders]
WHERE ReferenceNumber in
(SELECT ReferenceNumber FROM [ypolive_Integration].[dbo].[OrderHeaders]
GROUP BY ReferenceNumber HAVING COUNT(*)>1) 
AND PremiseId in (SELECT PremiseId FROM [ypolive_Integration].[dbo].[OrderHeaders] GROUP BY PremiseId HAVING COUNT(*)>1) 
AND DatePlaced in (SELECT DatePlaced FROM [ypolive_Integration].[dbo].[OrderHeaders] GROUP BY DatePlaced HAVING COUNT(*)>1)

answered Aug 03 '17 at 09:48

Mehul Vasa

51
9

This is way over-complicated, don't you think? – Bartosz X Aug 03 '17 at 10:14
But I don't think there is any other way to do so :) – Mehul Vasa Aug 03 '17 at 10:44
This is why CTE is the best option you have here, just check my answer, solution I put there is used a lot across SQL Server devs as in easy to set up and to read --> easy to debug after a time – Bartosz X Aug 03 '17 at 10:47

How to find duplicate data in a table

4 Answers4