Re: For each key, find row with highest value of other field - Mailing list 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.