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

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

  • Make SQL Intellisense know the current database
  • MySQL trigger to update SQL Server table
  • If not exists then insert else show message “Already exists”
  • How to turn separate year, month and day columns into a single date?
  • Where to set a UTC datetime value in n-tier application: Presentation Layer, Domain, or Database?
  • What is better to return single value from stored procedure to .Net: OUTPUT parameter or ExecuteScalar?
  • 8 Solutions collect form web for “Delete the 'first' record from a table in SQL Server, without a WHERE condition”

    WITH  q AS
            SELECT TOP 1 *
            FROM    mytable
            /* You may want to add ORDER BY here */
    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.

    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:

    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.

    Define “First”? If the table has a PK then it will be ordered by that, and you can delete by that:

        Data NVARCHAR(50) NOT NULL
    SELECT 'Hello' UNION
    SELECT 'World' 

    If the table has no PK, then ordering won’t be guaranteed…

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

    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“.

    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 (
            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.


    DELETE Field FROM Table WHERE Field IN (SELECT TOP 1 Field FROM Table ORDER BY Field DESC)
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.