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

I would think to do it like
SELECT DISTINCT ON (val) val,date ORDER BY date DESC, val ASC

I haven't tested this, but it's similar to things I've done recently,
and I'm pretty sure this will do what you want.

On Mon, Sep 12, 2011 at 8:54 PM, Reid Thompson <jreidthompson(at)gmail(dot)com> 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
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
-----BEGIN GEEK CODE BLOCK-----
Version: 3.12
GIT d+ s: a-- C++++(++) UL+++ P++++$ L+++>++++ E- W+ N o? K w--- O-
M- V? PS+++ PE(-) Y+ PGP->+++ t+>++ 5+++++ X(+) R>+ tv b+
DI++ D++>+++ G+ e* h! !r y**
------END GEEK CODE BLOCK------