Find Duplicate Rows
-
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
-
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
-
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*/ -
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/
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply