Re: simple query question: return latest

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.