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. On 9/12/2011 8:54 PM, 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: 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
