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/ ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
