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@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)
>
>