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