I assume the
start_date column corresponds to the "age" of the record, but the queries would be easy to change to use the
end_date. I also assume that the combination of
Id and
start_date is unique in the table (otherwise you could have two or more records for the same id with the dame date, and we couldn't distinguish between them).
There are several possibilities. Firstly, you could use a subquery which would select
id and
max(start_date) grouping by
id from the table, and the outer query would select all records for whose the
id and
start_date appear in the subquery. Something like this:
The thing to note here is that you can use more than one column with the
IN operator (just put the list of columns into parentheses).
Second option would be to use analytic function to compute the rank of each record and only select records with rank equal to 1. We need to use subquery again though, because analytic functions cannot appear in the
where clause:
Note that we sort the records by age in descending order, because we want the newest record (the one with the highest date) to have a rank of 1.
Analytic functions are immensely powerful. The full documentation is
here, but if you find the text to be too technical,
you should be able to google lots of examples of using individual functions, eg.
oracle rank function.
