Re: simple query question: return latest

On Nov 11, 2004, at 5:09 PM, Michael Glaesemann wrote:

> 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;

Unless I'm missing something, this returns every listing for color=red,
in max order.
So if I want the ONE most recent entry, is this something I have to
offload to my app
that parses the returned rows?  Or is there a function in postgres that
can return THE
most recent entry?


>
> 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.

Hmm.  Good tip.  Bad example terminology.

Thanks!
Scott




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