Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.

From: Reid Thompson <jreidthompson(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
Date: 2011-09-13 00:54:16
Message-ID: 4E6EA9B8.6050001@ateb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Could someone point me in the right direction..
Thanks - reid

Given the example data,
how do I write a query that will give me the resultset:

1 2011-01-01
2 2011-01-06
3 2011-01-05
4 2011-01-09
5 2011-01-05
6 2011-01-08

I.E. for each distinct val, return the record with the most recent date.

ex data
val date
1 2011-01-01
2 2011-01-02
3 2011-01-03
4 2011-01-04
5 2011-01-05
5 2011-01-01
4 2011-01-02
6 2011-01-03
4 2011-01-04
3 2011-01-05
2 2011-01-06
4 2011-01-07
6 2011-01-08
4 2011-01-09
5 2011-01-01
2 2011-01-02
4 2011-01-03
2 2011-01-04
1 2011-01-01
2 2011-01-02
3 2011-01-03
4 2011-01-04
3 2011-01-05
1 2011-01-01
2 2011-01-02
3 2011-01-03
4 2011-01-04
5 2011-01-01
-----------------------------------

$ cat sampledata|sort -k1,2
1 2011-01-01
1 2011-01-01
1 2011-01-01
2 2011-01-02
2 2011-01-02
2 2011-01-02
2 2011-01-02
2 2011-01-04
2 2011-01-06
3 2011-01-03
3 2011-01-03
3 2011-01-03
3 2011-01-05
3 2011-01-05
4 2011-01-02
4 2011-01-03
4 2011-01-04
4 2011-01-04
4 2011-01-04
4 2011-01-04
4 2011-01-07
4 2011-01-09
5 2011-01-01
5 2011-01-01
5 2011-01-01
5 2011-01-05
6 2011-01-03
6 2011-01-08

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Phoenix Kiula 2011-09-13 01:48:23 Foreign key check only if not null?
Previous Message Tom Lane 2011-09-12 23:42:30 Re: Compatibility 9.1rc and 9.1.0