On Thu, 11 Nov 2004, Scott Frankel wrote: > > 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.
No. This returns one row having the maximum date. The GROUP BY means that you would get one row per color, but the where clause basically means there is only the one. However, it does not extend to getting other attributes of that row. You can do something like the subselect already mentioned in another mail, or use if you can use a PostgreSQL extension, you might want to look into DISTINCT ON which will often be faster. Something like: select DISTINCT ON (color) * from giventable where color='red' order by color, date desc; should give the attributes of the one row with color='red' having the highest date. With a bit of work (reverse opclasses), one can make an index which can be used to provide the filtering and ordering for such queries. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend
