sql query to get latest record for each id

Question

I have one table. From that I need to get latest "Date" for each "id". I wrote query for One id. But I don't know how to apply for multiple ids.(I mean for each id)

My query for one id is (say table name is tt):

select * from (
   SELECT DISTINCT id ,date FROM tt  
   WHERE Trim(id) ='1000082'
   ORDER BY date desc
) where rownum<=1;

Answer 1

If you have just two columns, aggregation is good enough:

select id, max(date) max_date
from mytable
group by id

If you have more columns and you want the entire row that has the latest date for each id, then one option uses a correlated subquery for filtering:

select t.*
from mytable t
where t.date = (select max(t1.date) from mytable t1 where t1.id = t.id)

Or you can use window functions, if your database supports them:

select *
from (select t.*, row_number() over(partition by id order by date desc) rn from mytable t) t
where rn = 1