Re: Need help with what I think is likely a simple query - for each distinct val, return only one record with the most recent date. - Mailing list pgsql-general

Ack -- i flubbed the subject and sample.
The sample data should be

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

resultset:

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

where any one of these 3

1    1       2011-01-01
1    1       2011-01-01
1    3       2011-01-01

or any one of these 2
3    1       2011-01-05
3    2       2011-01-05

are suitable for val = 1, val = 3 respectively.





On 9/12/2011 8:54 PM, Reid Thompson wrote:
> 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
>
>