Postgresql extract last row for each id

Question

Suppose I've next data

  id    date          another_info   1     2014-02-01         kjkj   1     2014-03-11         ajskj   1     2014-05-13         kgfd   2     2014-02-01         SADA   3     2014-02-01         sfdg   3     2014-06-12         fdsA 

I want for each id extract last information:

  id    date          another_info   1     2014-05-13         kgfd   2     2014-02-01         SADA   3     2014-06-12         fdsA 

How could I manage that?

Answer 1

The most efficient way is to use Postgres' distinct on operator

select distinct on (id) id, date, another_info from the_table order by id, date desc; 

If you want a solution that works across databases (but is less efficient) you can use a window function:

select id, date, another_info from (   select id, date, another_info,           row_number() over (partition by id order by date desc) as rn   from the_table ) t where rn = 1 order by id; 

The solution with a window function is in most cases faster than using a sub-query.

Answer 2

select *  from bar  where (id,date) in (select id,max(date) from bar group by id) 

Tested in PostgreSQL,MySQL