Uisng exists in sql to find duplicates, is there a cleaner way?

Question

See script below to find duplicates in SQL Server DB. Is there a cleaner way?

select itemnum 
from matusetrans a
where exists (select null 
              from matusetrans b 
              where a.itemnum = b.itemnum 
                and a.actualdate = b.actualdate 
                and a.matusetransid != b.matusetransid 
                and (a.rotassetnum = b.rotassetnum 
                     or (a.rotassetnum is null and b.rotassetnum is null))    
                and a.quantity = b.quantity)
group by itemnum

Please define "duplicates" clearly. Are you referring to two records where all the columns in the row match? — Riaan van Zyl, Jul 04 '16 at 13:34
Hi Mfusiki, yes duplicates where the criteria within the code match and the count is > 1. — WRD299, Jul 04 '16 at 13:36
Please refer to this thread: http://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Riaan van Zyl, Jul 04 '16 at 13:37
With this thread I am unable to identify the unique itemnum. — WRD299, Jul 04 '16 at 13:38
If you are checking for duplicate rows the item number will never truly be unique? — Riaan van Zyl, Jul 04 '16 at 13:44

score 1 · Answer 1 · answered Jul 04 '16 at 13:43

1

You could try:

SELECT itemnum
FROM matusetrans
GROUP BY [ColumnNames]
HAVING 
COUNT(*) > 1

answered Jul 04 '16 at 13:43

Riaan van Zyl

538
8
17

score 1 · Answer 2 · answered Jul 04 '16 at 14:27

Assuming you want to find duplicate itemnum in table,Please use below query

SELECT itemnum
FROM matusetrans
GROUP BY [ItemNum]
HAVING COUNT(ItemNum) > 1

Using HAVING COUNT(*) > 1 may give you result as all are distinct if there are any Datetime columns like order datetime which generally varies per record.

Thanks, Sree

score 0 · Accepted Answer · answered Jul 04 '16 at 14:32

0

Another possibility (but not neccessarily "cleaner") might be

WITH cte AS(
  SELECT columns, ROW_NUMBER() OVER (PARTITION BY columns ORDER by columns) AS RowIdx
    FROM matusetrans
    GROUP BY columns
)
SELECT *
  FROM cte
  WHERE RowIdx > 1

answered Jul 04 '16 at 14:32

Tyron78

4,117
2
17
32

Uisng exists in sql to find duplicates, is there a cleaner way?

3 Answers3