Find Duplicate Rows

Question

Post reply

Find Duplicate Rows

JP789

SSC Veteran

Points: 222
More actions
May 25, 2025 at 10:17 pm

#4597293

What’s the best way to find duplicate rows in a sql query? I heard there is a way where RC is used and it gives RC 1 for the first row and RC 2 for the duplicate row. But would like to know the syntax for it.
I tried:
select itemnumber , count (itemnumber ) as count
From table1
Group by itemnumber
Having count (itemnumber) > 1

Are there other ways or a better way to query a table to find duplicate rows?
Thanks,
JP

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135549 More actions · Answer 1

Define duplicate row !

Otherwise, have a look at ranking functions

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jo Pattyn SSC-Dedicated Points: 32468 More actions · Answer 2

The ranking function solve this as Johan mentioned

WITH SELECTIE AS 
( 
SELECT mypk ...
  ,ROW_NUMBER() over 
  ( 
  PARTITION BY column1, column2 ... /* defines the key */
  ORDER BY  column3 /* defines the order */
  ) myrank 
  FROM mytable
) 
,DUBBELS AS 
( 
SELECT * 
FROM SELECTIE  
WHERE myrank > 1  /* the one you want to keep has myrank = 1 */
)
delete from dubbels /* deletes all pk with myrank, keeping the original with rank 1*/

rothj Hall of Fame Points: 3512 More actions · Answer 3

Window function is another way:

-- Selecting Duplicate Records
With CTE_Employee
AS (Select Employee_ID,
           Employee_Name,
           Employee_Dept,
           ROW_NUMBER() OVER (PARTITION BY Employee_ID,
                                           Employee_Name,
                                           Employee_Dept
                              ORDER BY Employee_ID,
                                       Employee_Name,
                                       Employee_Dept
                             ) AS RowNumber
    from #Employee
   )
SELECT Employee_ID,
       Employee_Name,
       Employee_Dept
FROM CTE_Employee
WHERE RowNumber > 1;

Here's a link that has your way too: https://dataschool.com/learn-sql/find-duplicates/