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@myrealbox.com>
To: "Scott Frankel" <leknarf@pacbell.net>
Cc: <pgsql-general@postgresql.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@postgresql.org)
>
>