select distinct on (Key) Key, Date, Value from <<table name>> order by Key, Date desc
MYDATABASE=> create table aaa (key varchar(1), date date, value text); CREATE TABLE Time: 1518.002 ms MYDATABASE=> insert into aaa (key, date, value) values ('A', '2008-05-01', 'foo'); INSERT 0 1 Time: 1.125 ms MYDATABASE=> insert into aaa (key, date, value) values ('A', '2008-04-01', 'bar'); INSERT 0 1 Time: 0.290 ms MYDATABASE=> insert into aaa (key, date, value) values ('A', '2008-03-01', 'foo'); INSERT 0 1 Time: 0.310 ms MYDATABASE=> insert into aaa (key, date, value) values ('B', '2008-03-01', 'baz'); INSERT 0 1 Time: 0.304 ms MYDATABASE=> insert into aaa (key, date, value) values ('B', '2008-02-01', 'bar'); INSERT 0 1 Time: 0.330 ms MYDATABASE=> insert into aaa (key, date, value) values ('C', '2008-06-03', 'foo'); INSERT 0 1 Time: 0.298 ms MYDATABASE=> insert into aaa (key, date, value) values ('C', '2008-04-04', 'baz'); INSERT 0 1 Time: 0.295 ms MYDATABASE=> insert into aaa (key, date, value) values ('C', '2008-03-04', 'bar'); INSERT 0 1 Time: 0.319 ms MYDATABASE=> commit; COMMIT Time: 569.591 ms MYDATABASE=> select * from aaa; key | date | value -----+------------+------- A | 2008-05-01 | foo A | 2008-04-01 | bar A | 2008-03-01 | foo B | 2008-03-01 | baz B | 2008-02-01 | bar C | 2008-06-03 | foo C | 2008-04-04 | baz C | 2008-03-04 | bar (8 rows) Time: 0.520 ms MYDATABASE=> select distinct on (key) key, date, value from aaa order by key, date desc; key | date | value -----+------------+------- A | 2008-05-01 | foo B | 2008-03-01 | baz C | 2008-06-03 | foo (3 rows) Time: 0.524 ms -Mark On Sat, 2008-10-04 at 00:25 +0530, Raj Mathur wrote: > I have some data of the form: > > Key | Date | Value > A | 2008-05-01 | foo * > A | 2008-04-01 | bar > A | 2008-03-01 | foo * > B | 2008-03-04 | baz > B | 2008-02-04 | bar > C | 2008-06-03 | foo * > C | 2008-04-04 | baz > C | 2008-03-04 | bar > > Is there any way to select only the rows marked with a (*) out of > these > without doing a join? I.e. I wish to find the row with the highest > Date for each Key and use the Value from that. > > Regards, > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql