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: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of Scott Frankel
> Sent: Thursday, November 11, 2004 11:13 PM
> To: [EMAIL PROTECTED]
> 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
> [EMAIL PROTECTED]
>
Confidentiality Notice
The information contained in this e-mail is confidential and intended for use
only by the person(s) or organization listed in 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 intended recipient, is strictly
prohibited.
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
