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@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > 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------ -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
