Thread

Antw: using max() aggregate

Gerhard Dieringer <dieringg@eba-haus.de> — 2000-06-16T07:28:27Z

Louis-David Mitterrand - ldm@apartia.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