If 2 days are equal for color red, you still would get 2 rows returned.
Maybe the below is accurate.
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
) LIMIT 1
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Frankel
> Sent: Thursday, November 11, 2004 11:13 PM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] simple query question: return latest
>
>
>
> ORDER BY DESC LIMIT 1 is much simpler and more readable than
> a sub-query.
> Though the sub-query approach looks to be a good template for
> ensuring more accurate results by being more explicit.
>
> Thanks to all who responded!
> Scott
>
>
>
> SELECT * FROM colortable WHERE color = 'red' ORDER BY date
> DESC LIMIT 1;
>
>
>
> 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
> )
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
Confidentiality Notice
The information contained in this e-mail is confidential and intended for use only by the person(s) or organization
listedin the address. If you have received this communication in error, please contact the sender at O'Neil &
Associates,Inc., immediately. Any copying, dissemination, or distribution of this communication, other than by the
intendedrecipient, is strictly prohibited.