JMzance - 8 months ago 71

SQL 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

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.)