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

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)