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

select distinct on (a) a,b from the_table order by a, b desc? — user330315
– user330315, Commented Jun 20, 2016 at 8:21
Most up to date: select * from ztable t where not exists (select * from ztable x where x.a = t.a and x.b > t.b); — joop
– joop, Commented Jun 20, 2016 at 8:36
a_horse_with_no_name - will the ordering be applied before or after the DISTINCT? @joop could you explain how that works please and I'll mark it as accepted — JMzance
– JMzance, Commented Jun 20, 2016 at 8:55
@JMzance: the order is used to find the distinct values. It is essential for distinct on () to work properly. Have a look at all the other questions tagged with greatest-n-per-group for Postgres. — user330315
– user330315, Commented Jun 20, 2016 at 9:46

Laurenz Albe · Accepted Answer · 2016-06-20 09:44:11Z

3

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

answered Jun 20, 2016 at 9:44

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Binita Bharati · Accepted Answer · 2021-10-28 02:04:53Z

1

As posted by the upvoted answers, SELECT DISTINCT ON (a) a, b FROM the_table ORDER BY a, b DESC works on Postgre 12. However, I am posting this answer to highlight few important points:

The results will be sorted based on column a; not column b.
Within each result row, the most recent (highest value) for column b would be picked.
In case, someone wants to get the most recent value for column b on the entire result set, in sql, we can run : SELECT MAX(b) from (SELECT DISTINCT ON (a) a, b FROM the_table ORDER BY a, b DESC).

answered Oct 28, 2021 at 2:04

Binita Bharati

6,0861 gold badge48 silver badges28 bronze badges

Collectives™ on Stack Overflow

Postgres select distinct based on timestamp

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related