Antw: using max() aggregate

Louis-David Mitterrand - ldm(at)apartia(dot)org - http://www.apartia.fr wrote:

> Hello,
>
> I am trying to return the most recently updated record from a table:
>
> SELECT max(stopdate) FROM auction;
>
> and this works but only returns the timestamp, however if I try to get
> another column with the aggregate it fails:
>
> SELECT title,max(stopdate) FROM auction;
> ERROR: Attribute auction.title must be GROUPed or used in an aggregate function
>
> Ok, so I group it now:
>
> SELECT title,max(stopdate) FROM auction GROUP BY title;
> title | max
> ---------------+------------------------
> dfsdfsdf | 2000-07-10 05:00:00+02
> dssdfsdfsdfsf | 2000-07-09 16:00:00+02
> sdfsdfsdfsdf | 2001-04-10 15:00:00+02
> (3 rows)
>
> But the problem is that I now get three rows when I only want the max()
> item.
>
> How should I do it?
>
> Thanks in advance,

I didn't test it, but something like

SELECT title,stopdate
FROM auction
WHERE stopdate = (SELECT max(stopdate) FROM auction);

should work.

--------
Gerhard