Delete the first record from a table in SQL Server without a WHERE condition

Question

Is it possible to delete the  first  record from a table in SQL Server  without using any WHERE condition and without using a cursor

User · Answer

Define  First   If the table has a PK then it will be ordered by that  and you can delete by that   DECLARE  TABLE TABLE       ID INT IDENTITY 1 1  NOT NULL PRIMARY KEY      Data NVARCHAR 50  NOT NULL    INSERT INTO  TABLE Data  SELECT  Hello  UNION SELECT  World    SET ROWCOUNT 1 DELETE FROM  TABLE SET ROWCOUNT 0  SELECT   FROM  TABLE   If the table has no PK  then ordering won t be guaranteed

User · Answer

SQL-92   DELETE Field FROM Table WHERE Field IN  SELECT TOP 1 Field FROM Table ORDER BY Field DESC

User · Answer

depends on your DBMS  people don t seem to know what that is nowadays   -- MYSql  DELETE FROM table LIMIT 1  -- Postgres  DELETE FROM table LIMIT 1  -- MSSql  DELETE TOP 1  FROM table  -- Oracle  DELETE FROM table WHERE ROWNUM   1

User · Answer

No  AFAIK  it s not possible to do it portably   There s no defined  first  record anyway - on different SQL engines it s perfectly possible that  SELECT   FROM table  might return the results in a different order each time

User · Answer

What do you mean by    first  record from a table     There s no such concept as  first record  in a relational db  i think   Using MS SQL Server 2005  if you intend to delete the  top record   the first one that is presented when you do a simple   select   from tablename     you may use  delete top 1  from tablename     but be aware that this does not assure which row is deleted from the recordset  as it just removes the first row that would be presented if you run the command  select top 1  from tablename

User · Answer

Similar to the selected answer  a table source can be used  in this case a derived query   delete from dd from       select top 1       from my table   dd   Feel free to add orderbys and conditions   For the next example  I ll assume that the restriction on  where  is due to not wanting to select a row based on its values   So assuming that we want to delete a row based on position  in this case the first position    delete from dd from       select                    row   row number   over  order by  select 1       from my table   dd where row   1   Note that the  select 1  makes it the sort order that the tables or indexes are in   You can replace that with a newid to get fairly random rows   You can also add a partition by to delete the top row of each color  for example

User · Answer

Does this really make sense  There is no  first  record in a relational database  you can only delete one random record

User · Answer

WITH  q AS                   SELECT TOP 1           FROM    mytable            You may want to add ORDER BY here              DELETE FROM    q   Note that  DELETE TOP  1  FROM   mytable   will also work  but  as stated in the documentation      The rows referenced in the TOP expression used with INSERT  UPDATE  or DELETE are not arranged in any order    Therefore  it s better to use WITH and an ORDER BY clause  which will let you specify more exactly which row you consider to be the first

[sql] Delete the 'first' record from a table in SQL Server, without a WHERE condition

Examples related to sql

Examples related to sql-server