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(at)postgresql(dot)org)
>