Deleting top rows only. SQL Server

Question

How do I delete only the top row of a table in SQL Server?

I did:

set rowcount 1;
delete * from table;
set rowcount 0;

But I am not sure if its a good way to do it.

Is there any better way to accomplish that?

Answer 1

UPDATE: oops! @gbn is right, my ORDER BY sample was broken! Updating with correct code sample.

In the most common case where the "top" is based the order of values in a particular column or columns, you can use a CTE and ROW_NUMBER to simulate an ordered TOP:

WITH cte AS
(
  SELECT *, ROW_NUMBER() OVER(ORDER BY SomeColumn, OtherColumn) AS RowNum
  FROM table
)
DELETE FROM cte
WHERE RowNum <= 1;

See Itzik Ben-Gan's SQLMag article on TOP for more info on this suggested pattern.

If you simply want to delete any row of a set of duplicates, or just want to delete a random row for some reason, then it's safe to omit the ORDER BY and do something simpler:

DELETE TOP (1) FROM table

Quoting from the DELETE docs on MSDN:

TOP ( expression ) [ PERCENT ] Specifies the number or percent of random rows that will be deleted. expression can be either a number or a percent of the rows. The rows referenced in the TOP expression used with INSERT, UPDATE, or DELETE are not arranged in any order.

Parentheses delimiting expression in TOP are required in INSERT, UPDATE, and DELETE statements. For more information, see TOP (Transact-SQL).

Answer 2

The problem with this approach is that it will delete one row at random, you need to be more specific (such as using an order by clause) to make sure you are deleting what you want. Better yet - add a where clause with the primary key for the row you really want to delete and drop the "rowcount" clause altogether.

Answer 3

It seems like your question begs what the sorting criterion should be? When you say 'top row' what is that order in respect to?

Answer 4

• TOP is meaningless without ORDER BY

• Use WITH TIES to deal with joint top

• You can't use ORDER BY directly in a DELETE so you have to workaround it

Like this:

DELETE foo
FROM (SELECT TOP 1 /*WITH TIES*/ * FROM Mytable ORDER BY WhatDecidesTop) foo;

;WITH Foo AS
(
    SELECT TOP 1 /*WITH TIES*/ * FROM Mytable ORDER BY WhatDecidesTop
)
DELETE foo;