sim sim - 5 months ago 10
SQL Question

Delete all records except the most recent one?

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
DELETE FROM student
WHERE ApplicationDateTime <> (SELECT max(ApplicationDateTime) 
                              FROM student s2
                              WHERE s2.StudentID  = student.StudentID)
Comments