Postgres select distinct based on timestamp

Question

I have a table with two columns a and b where a is an ID and b is a timestamp. I need to select all of the a's which are distinct but I only care about the most up to date row per ID.

I.e. I need a way of selecting distinct a's conditional on the b values.

Is there a way to do this using DISTINCT ON in postgres?

Cheers

Answer 1

Like @a_horse_with_no_name suggests, the solution is

SELECT DISTINCT ON (a) a, b FROM the_table ORDER BY a, b DESC

As the manual says,

Note that the "first row" of a set is unpredictable unless the query is sorted on enough columns to guarantee a unique ordering of the rows arriving at the DISTINCT filter. (DISTINCT ON processing occurs after ORDER BY sorting.)