CODEWITHSUNDEEP
sql-server
bez91
bez91

Reputation: 136

Delete Last Row in a Table - SQL

I have the following basic SQL query:

DELETE TOP (1)
FROM dbo.userPasswordHistory
WHERE UserId IN (SELECT Id FROM dbo.user WHERE LogonId = @UserLogonId)

I want to delete the most recent entry in the dbo.userPasswordHistory table but DELETE TOP (1) is deleting the oldest entry, not the most recent. So for instance if the dbo.userPasswordHistory table contains 5 rows for a specified user then it is the most recent entry that I want to delete.

How would I order the table based on a column called LastModifiedTime or is there a way I can specify bottom or last?

Upvotes: 1

Views: 4654

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can do this with a subquery or CTE:

DELETE t
FROM (SELECT TOP (1) uph.*
      FROM dbo.userPasswordHistory uph
      WHERE UserId IN (SELECT Id
                       FROM dbo.user
                       WHERE LogonId = @UserLogonId
                      )
      ORDER BY LastModifiedTime DESC
     ) t;

Upvotes: 3

Related Questions