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.

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