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.
| От | jesuthefrog |
|---|---|
| Тема | 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. |
| Дата | |
| Msg-id | CAH_wARvp7ztE3eddhHDBYwJwQY9riiHuFD7Lc8ZwLnaB5-0=dw@mail.gmail.com обсуждение исходный текст |
| Ответ на | Need help with what I think is likely a simple query - for each distinct val, return only the record with the most recent date. (Reid Thompson <jreidthompson@gmail.com>) |
| Список | pgsql-general |
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------
В списке pgsql-general по дате отправления: