Re: simple query question: return latest

I interpreted the question slightly differently. I understood it to mean the
most recent instance of red which doesn't make much sense in this case but
let's say the table was

color | date | entered_by
--------+-----------------+---------------
red | 2004-01-19 | John
red | 2004-04-12 | Jane

and you wanted to pick up the row which Jane entered, then the statement
would be

SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE g.color = 'red'
AND g.date =
(SELECT MAX(g2.date)
FROM giventable g2
WHERE g2.color = g.color
)

or perhaps

SELECT g.color, g.date, g.entered_by
FROM giventable g
WHERE (g.color,g.date) =
(SELECT g2.color, MAX(g2.date)
FROM giventable g2
WHERE g2.color = 'red'
GROUP BY g2.color
)

etc. etc.

----- Original Message -----
From: "Michael Glaesemann" <grzm(at)myrealbox(dot)com>
To: "Scott Frankel" <leknarf(at)pacbell(dot)net>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Thursday, November 11, 2004 5:09 PM
Subject: Re: [GENERAL] simple query question: return latest

> Scott,
>
> On Nov 12, 2004, at 10:00 AM, Scott Frankel wrote:
>
> > color | date
> > --------+------------
> > red | 2004-01-19
> > blue | 2004-05-24
> > red | 2004-04-12
> > blue | 2004-05-24
> >
> >
> > How do I select the most recent entry for 'red'?
> >
>
> SELECT color, MAX(date)
> FROM giventable
> WHERE color = 'red' -- omit this line if you'd like to see the latest
> date for each color
> GROUP BY color;
>
> OT hint: You might want to take a look at the list of PostgreSQL
> Keywords in the documentation and avoid using them (such as date) to
> help you avoid naming issues in the future.
>
> Hope this helps.
>
> Michael Glaesemann
> grzm myrealbox com
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>
>