Ben
Ben

Reputation: 83

Delete last record from SQL Server?

I want to delete the last row from my table which I have 'no' is the auto number. Could anyone help me please?

no | name | sex | phone|
1    Jack    m     343
2     tim    f     233

Upvotes: 4

Views: 9714

Answers (3)

Chris Singleton
Chris Singleton

Reputation: 165

Another Way of doing it (since [no] column is already numbered):

          ;WITH CTE
          AS 
          (
          SELECT MAX(no)  OVER(ORDER BY no ASC) AS MaxDelete FROM TableName
          )
          DELETE FROM TableName WHERE no = MaxDelete

Upvotes: 0

DarkRob
DarkRob

Reputation: 3833

You may try this

DELETE FROM table
WHERE  no = (SELECT Max(no) FROM table)  

Upvotes: 6

DxTx
DxTx

Reputation: 3357

Another way of doing it.

DELETE FROM TableName
WHERE  ColumnName IN (SELECT TOP 1 ColumnName 
                      FROM   TableName
                      ORDER  BY ColumnName DESC);  

Or you can use this with precaution

DELETE FROM TableName
WHERE  ColumnName = Ident_current('TableName')  

Note: This only works if that table has enabled Auto-increment.

Upvotes: 3

Related Questions