Delete All Records Except the Most Recent One

Delete all records except the most recent one?

DELETE FROM student
WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime) 
                              FROM student s2
                              WHERE s2.StudentID  = student.StudentID)

---

Given the long discussion in the comments, please note the following:

The above statement will work on any database that properly implements statement level read consistency regardless of any changes to the table while the statement is running.

Databases where I definitely know that this works correctly even with concurrent modifications to the table: Oracle (the one which this question is about), Postgres, SAP HANA, Firebird (and most probably MySQL using InnoDB). Because they all guarantee a consistent view of the data at the point in time when the statement started. Changing the <> to < will not change anything for them (including Oracle which this question is about)

For the above mentioned databases, the statement is not subject to the isolation level because phantom reads or non-repeatable reads can only happen between multiple statements - not within a single statement.

For database that do not implement MVCC properly and rely on locking to manage concurrency (thus blocking concurrent write access) this might actually yield wrong results if the table is updated concurrently. For those the workaround using < is probably needed.

(SQL) Delete all rows except the newest one

If you want to delete all the rows the SQL is :

Delete  from theTable;

If you want to exclude the newest, hoping that you have an id you write like this:

Delete  from theTable where id <> (Select max (id) from theTable)

Delete all rows except 100 most recent ones

You can use one of the following:

-- offset clause
WITH goners AS (
    SELECT *
    FROM Logs
    ORDER BY DateTime DESC
    OFFSET 100 ROWS 
)
DELETE FROM goners

-- numbered rows
WITH goners AS (
    SELECT ROW_NUMBER() OVER(ORDER BY DateTime DESC) AS rn, Logs.*
    FROM Logs
)
DELETE FROM goners
WHERE rn > 100

-- nth value
-- note that this "keeps" all rows that tie for last place
DELETE FROM Logs
WHERE DateTime < (
    SELECT MIN(DateTime)
    FROM (
        SELECT TOP 100 DateTime
        FROM Logs
        ORDER BY DateTime DESC
    ) AS x
)

How to delete all rows in a group except the newest one?

delete from mytable
where id not in 
(
  select max(id)
  from mytable
  group by application_id
)

MySQL - Efficiently delete all records except last N

Try DELETE JOIN:

delete a from access a left join (
    select id
    from access
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to keep top 1000 records of a given user (say 123) from deleting :

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null;

If you want to delete rows only for user 123 except the top 1000 for that user:

delete a from access a left join (
    select id
    from access
    where userid = 123
    order by id desc limit 1000
) b on a.id = b.id
where b.id is null
and a.userid = 123;

SQL query: Delete all records from the table except latest N?

You cannot delete the records that way, the main issue being that you cannot use a subquery to specify the value of a LIMIT clause.

This works (tested in MySQL 5.0.67):

DELETE FROM `table`
WHERE id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`
    ORDER BY id DESC
    LIMIT 42 -- keep this many records
  ) foo
);

The intermediate subquery is required. Without it we'd run into two errors:

1. SQL Error (1093): You can't specify target table 'table' for update in FROM clause - MySQL doesn't allow you to refer to the table you are deleting from within a direct subquery.
2. SQL Error (1235): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery' - You can't use the LIMIT clause within a direct subquery of a NOT IN operator.

Fortunately, using an intermediate subquery allows us to bypass both of these limitations.

---

Nicole has pointed out this query can be optimised significantly for certain use cases (such as this one). I recommend reading that answer as well to see if it fits yours.

Delete all items in a database except the last date

You can use ROW_NUMBER() analytic function ( as using DB version 8+ ) :

DELETE lg FROM `logging` AS lg
 WHERE lg.`id` IN
      ( SELECT t.`id` 
          FROM
         (
          SELECT t.*,
                 ROW_NUMBER() OVER (ORDER BY `time` DESC) as rn
            FROM `logging` t
       --  WHERE `level` = @lvl -- optionally add this line to restrict for a spesific value of `level`
         ) t
         WHERE t.rn > 1
       )

to delete all of the rows except the last inserted one(considering id is your primary key column).

Delete all rows except 1 the most recent

Your delete statement appears to have a syntax error, try changing the query from

DELETE S1 FROM ...

to

DELETE FROM ...

UPDATE:

Try this statement as a replacement for yours:

DELETE FROM StateLogs WHERE Id NOT IN (SELECT TOP (1) Id FROM StateLogs ORDER BY Modified DESC)

Also just a heads up that you may have problems with insert/update operations against the table based on the schema you've provided because your primary key is larger than the maximum: http://msdn.microsoft.com/en-us/library/ms191241(v=sql.105).aspx

This could pretty cause mysterious runtime failures in your application.

Delete all rows except max(timestamp) on each day in mysql

The row with the highest timestamp appears to also have the highest Id.

DELETE FROM YourTable
WHERE Id NOT IN (SELECT MAX(Id) FROM YourTable GROUP BY currency, DATE(timestamp))

---

Related Topics