Delete every record except for the max

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 2005 Forums

Transact-SQL (2005)

Delete every record except for the max

Author

Topic

robc
Yak Posting Veteran

60 Posts

Posted - 2008-11-14 : 19:11:37

Hi,

I have some data I need to trim from my DB.

TABLE
userID,photoID,dateUploaded,displayStatus

I'd like to delete all records except the max(dateUploaded) where displayStatus = 1 for each user.

rc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 23:52:43

[code]DELETE t
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY userID ORDER BY dateUploaded DESC) AS Seq,userID,photoID,dateUploaded,displayStatus
WHERE displayStatus=1
)t
WHERE t.Seq>1[/code]

make sure you first put SELECT * instead of DELETE and check if they return only the intended records before you delete tham.