Antw: using max() aggregate

From: "Gerhard Dieringer" <DieringG(at)eba-haus(dot)de>
To: <pgsql-sql(at)postgresql(dot)org>
Cc: <cunctator(at)apartia(dot)ch>
Subject: Antw: using max() aggregate
Date: 2000-06-16 07:28:27
Message-ID: s949f34f.049@kopo001
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


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

Browse pgsql-sql by date

  From Date Subject
Next Message Louis-David Mitterrand 2000-06-16 08:11:21 Re: Antw: using max() aggregate
Previous Message James Carpenter 2000-06-16 06:41:14 Determining Array size