question

KunjanSanghavi avatar image
KunjanSanghavi asked

Delete rows

I have one assignment ques. I am trying to search hard but not able to figure out table Courses(CourseName, Description, Semester), write an SQL query which deletes the duplicate rows with the same CourseName and leaves the first one. Do not use rowcount. Assume Semester has unique values even if Coursename is the same. Can anybody please help me? Thanks in advance
homeworkdelete-duplicates
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Something like this should work: ;WITH CTE AS ( SELECT CourseName, Description, Semester, ROW_NUMBER() OVER(PARTITION BY coursename ORDER BY Semester) as RowNum FROM @t ) DELETE CTE WHERERowNum > 1
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image ThomasRushton ♦♦ commented ·
Heh. I blogged something like this a while ago...
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
I first saw the construct here on AskSSC som days or weeks ago. I think it was @Håkan Winther who posted an answer using CTE and delete for de-duplication. I think it's a very elegant way of doing it.
0 Likes 0 ·
Håkan Winther avatar image Håkan Winther commented ·
@Magnus Ahlkvist, here is the link to the question you mentioned: http://ask.sqlservercentral.com/questions/80810/identify-and-remove-duplicates-help
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.