Thread: Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date.
From
Reid Thompson
Date:
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
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.
From
Toby Corkindale
Date:
On 13/09/11 10:54, 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: [snip] > I.E. for each distinct val, return the record with the most recent date. Isn't it something simple like this? SELECT val, max(date) GROUP BY val; -Toby
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.
From
jesuthefrog
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@gmail.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@postgresql.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------