Re: simple query question: return latest

On Thu, Nov 11, 2004 at 05:00:46PM -0800, Scott Frankel wrote:

> How does one return the latest row from a table, given multiple entries
> of varying data?
> i.e.: given a table that looks like this:
>
> 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'?

One way would be to sort by date and use a LIMIT clause:

SELECT * FROM colortable WHERE color = 'red' ORDER BY date DESC LIMIT 1;

If you want the most recent entry for all colors then you could use
SELECT DISTINCT ON:

SELECT DISTINCT ON (color) * FROM colortable ORDER BY color, date DESC;

In either case, if multiple records have the same date and the ORDER BY
clause isn't specific enough to guarantee a certain order, then it's
indeterminate which record you'll get.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/