Skip site navigation (1) Skip section navigation (2)

Re: For each key, find row with highest value of other field

From: Mark Roberts <mailing_lists(at)pandapocket(dot)com>
To: raju(at)linux-delhi(dot)org
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: For each key, find row with highest value of other field
Date: 2008-10-03 19:57:19
Message-ID: 1223063840.12105.418.camel@localhost (view raw or flat)
Thread:
Lists: pgsql-sql
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,
> 


In response to

pgsql-sql by date

Next:From: Marc MaminDate: 2008-10-03 20:29:12
Subject: Re: Query how-to
Previous:From: Fernando HeviaDate: 2008-10-03 19:19:04
Subject: Re: For each key, find row with highest value of other field

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group