Is it possible to delete all other rows but keep one which has oldest date in it ?
ID Name Birthdate
1 A 20160101
2 B 20160202
3 C 20160303
returns 3 and deletes all other rows
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.