Aiden Aiden - 29 days ago 6
SQL Question

Use Date from most recent and delete others SQL

Is it possible to delete all other rows but keep one which has oldest date in it ?

E.g.

Person

ID Name Birthdate

1 A 20160101
2 B 20160202
3 C 20160303


Is there any query that returns me ID of person row with OLDEST birthdate and DELETE all other rows that is

returns 3 and deletes all other rows


If all birthdays are SAME date then return me row with LOWEST ID

Thanks

Aiden

Answer

Sure, if by DELETE you mean to remove records from the database and by RETURN you mean selecting a row, AND if you can use a batch of two queries, you could do:

DELETE Person
WHERE ID <> (
    SELECT TOP 1 ID
    FROM Person
    ORDER BY Birthdate DESC, ID
);

SELECT TOP 1 ID
FROM Person;

Ordering the subquery last for Id ASC garantees that, if there are equal Birthdates, only the lowest ID is returned.
The SELECT in the end will return the only remaining Person.

If you require just one query for both operations, then I don't think it's possible.