Delete all records except the most recent one?

DELETE FROM studentWHERE 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.

You can use row_number() (or rank() or dense_rank(), or even just the rownum pseudocolumn) to apply an order to the records, and then use that order to decide which to discard. In this case, ordering by applicationdatetime desc gives the application with the most recent date for each student the rank of 1:

select studentid, applicationid from (    select studentid, applicationid,        row_number() over (partition by studentid            order by applicationdatetime desc) as rn    from application)where rn = 1; STUDENTID APPLICATIONID---------- -------------         1         20002         2         20005

You can then delete anything with a rank higher than 1, which will preseve the records you care about:

delete from applicationwhere (studentid, applicationid) in (    select studentid, applicationid from (        select studentid, applicationid,            row_number() over (partition by studentid                order by applicationdatetime desc) as rn        from application    )    where rn > 1);3 rows deleted.

At first you can do so

DELETE FROM [student]           or [application]WHERE (studentid, applicationid) NOT IN (SELECT StudentID                                               ,MAX(ApplicationID)                                         FROM student                                             ,applicationgroup by StudentID);

but there is another solution to, you can create the backup table, after delete all records in your tables and after insert your data (what you want) with max values select in your tables.