sql - Delete all records except the most recent one?

Question

I have two DB tables in a one-to-many relationship. The data looks like this:

select * from student, application

Resultset:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20001         | 12 April 2011       |
| 1         | 20002         | 15 May 2011         |
| 2         | 20003         | 02 Feb 2011         |
| 2         | 20004         | 13 March 2011       |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

I want to delete all applications except for the most recent one. In other words, each student must only have one application linked to it. Using the above example, the data should look like this:

+-----------+---------------+---------------------+
| StudentID | ApplicationID | ApplicationDateTime |
+-----------+---------------+---------------------+
| 1         | 20002         | 15 May 2011         |
| 2         | 20005         | 05 June 2011        |
+-----------+---------------+---------------------+

How would I go about constructing my DELETE statement to filter out the correct records?

Answer 1

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.