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/