Обсуждение: For each key, find row with highest value of other field

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,

-- Raju
-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/      GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968
D0EFCC68 D17F
 
PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves

Howdy, Raju

Do you want somethin like this? Not sure if I fully understood your 
requests...

SELECT "Key",MAX(Date)
FROM t_yourTable
WHERE Value LIKE 'foo'
GROUP BY Key ;

Best,
Oliveiros
----- Original Message ----- 
From: "Raj Mathur" <raju@linux-delhi.org>
To: <pgsql-sql@postgresql.org>
Sent: Friday, October 03, 2008 7:55 PM
Subject: [SQL] For each key, find row with highest value of other field


>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,
>
> -- Raju
> -- 
> Raj Mathur                raju@kandalaya.org      http://kandalaya.org/
>       GPG: 78D4 FC67 367F 40E2 0DD5  0FEF C968 D0EF CC68 D17F
> PsyTrance & Chill: http://schizoid.in/   ||   It is the mind that moves
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
> 

> 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.
> 

This should do it:

Select value from table awhere date = (select max(b.date) from table b where b.key = a.key) q;

Regards,
Fernando.

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 | fooA   | 2008-04-01 | barA   | 2008-03-01 | fooB   | 2008-03-01 | bazB   |
2008-02-01| barC   | 2008-06-03 | fooC   | 2008-04-04 | bazC   | 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 | fooB   | 2008-03-01 | bazC   | 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,
>