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.
I’ve looked at a lot of different solutions, and in my humble opinion Aspose is the way to go. Here’s the link: http://aspose.com
subject: Oracle - Retriving only latest data from a table if there is a Duplicate.