Delete duplicated row insertions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Delete duplicated row insertions

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2006-12-05 : 16:33:01

Hi,
I have a following table, and rows with the same SectionID, RoomID, TimeID, DayID, PeriodID, and ScheduleTyID are delicately inserted. Column “SessionID” is a primary key of the table. So, on viewing the table, you see sectionID= 9634, 9636 have duplicate row insertions:

SectionID SessionID RoomID TimeID DayID PeriodID ScheduleTyID
----------| ----------| ----------| ----------| ----------| ----------| -----------|
9634 23444 21 135 6 30 2
9634 24312 21 135 6 30 2
9636 23401 9 71 8 15 3
9636 24269 9 71 8 15 3
9638 23180 9 108 13 29 4
I would like to remove row with sessionIDs of 23444 (bigger sessionIDs), 23401 of sectionIDs of 9634 and 9636. What is the best way (quick and less scripts) way to remove such duplicates?
Any help/hint is greatly appreciated.
JohnSql

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 16:43:36

23444 is bigger than 24312?

delete a
from yourtablenamehere a
inner join (select sectionid, min(sessionid) mid from yourtablenamehere group by sectionid) d
on d.sectionid = a.sectionid and d.mid <> a.sessionid

If you want to keep the biggest sessionid, just replace MIN with MAX.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources