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.

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

sigh -- looks like I fat fingered one of my resultset values above.
But, I think this gives me what I want:

test=# select distinct on (val1) val1, val2, val3 from (SELECT
max(val3) OVER (PARTITION BY val1), * FROM sampledata) as sq where val3
= max order by val1;
val1 | val2 | val3
------+------+------------
1 | 3 | 2011-01-01
2 | 2 | 2011-01-06
3 | 2 | 2011-01-05
4 | 2 | 2011-01-09
5 | 4 | 2012-01-01
6 | 1 | 2011-01-08
(6 rows)

val1 | val2 | val3
------+------+------------
1 | 3 | 2011-01-01
1 | 1 | 2011-01-01
1 | 1 | 2011-01-01
2 | 2 | 2011-01-02
2 | 1 | 2011-01-02
2 | 3 | 2011-01-04
2 | 1 | 2011-01-02
2 | 2 | 2011-01-06
2 | 2 | 2011-01-02
3 | 3 | 2011-01-03
3 | 3 | 2011-01-03
3 | 2 | 2011-01-05
3 | 1 | 2011-01-05
3 | 2 | 2011-01-03
4 | 1 | 2011-01-04
4 | 1 | 2011-01-02
4 | 3 | 2011-01-04
4 | 3 | 2011-01-07
4 | 3 | 2011-01-04
4 | 2 | 2011-01-09
4 | 1 | 2011-01-04
4 | 2 | 2011-01-03
5 | 4 | 2012-01-01
5 | 2 | 2011-01-05
5 | 3 | 2011-01-01
5 | 3 | 2011-01-01
6 | 1 | 2011-01-08
6 | 2 | 2011-01-03
(28 rows)