Delete duplicate rows from table

Question

Possible Duplicate:
Delete Duplicate SQL Records

I want to delete duplicate rows from a table

ID      uID     SID      USID
------------------------------
4391    007 Flight  ADM007
4392    007 Enquiry ADM007
6723    007 Flight  ADM007
6724    007 Enquiry ADM007
6725    008 Flight  ADM008
6726    008 Enquiry ADM008
4393    008 Flight  ADM008
4394    008 Enquiry ADM008
4395    009 Flight  ADM009
4396    009 Enquiry ADM009
6727    009 Flight  ADM009
6728    009 Enquiry ADM009

I want to keep only

4391    007 Flight  ADM007
4392    007 Enquiry ADM007
6725    008 Flight  ADM008
6726    008 Enquiry ADM008
4395    009 Flight  ADM009
4396    009 Enquiry ADM009

please give me suggestion

If you search StackOverflow for "Delete duplicate records" this problem has already been solved many times. — Adam Wenger, Oct 26 '12 at 05:34

score 1 · Answer 1 · answered Oct 26 '12 at 06:05

You can use a CTE.

with C as
(
  select row_number() over(partition by uID, SID, USID order by ID) as rn
  from YourTable
)
delete from C
where rn > 1

The rows left in YourTable will be

ID          uID         SID        USID
----------- ----------- ---------- ----------
4391        7           Flight     ADM007
4392        7           Enquiry    ADM007
4393        8           Flight     ADM008
4394        8           Enquiry    ADM008
4395        9           Flight     ADM009
4396        9           Enquiry    ADM009

Not exactly what you are looking for but as good as it gets without another column to order by.

Delete duplicate rows from table

1 Answers1