Re: Antw: using max() aggregate
От | Louis-David Mitterrand |
---|---|
Тема | Re: Antw: using max() aggregate |
Дата | |
Msg-id | 20000616101121.B6451@styx обсуждение исходный текст |
Ответ на | Antw: using max() aggregate ("Gerhard Dieringer" <DieringG@eba-haus.de>) |
Список | pgsql-sql |
On Fri, Jun 16, 2000 at 09:28:27AM +0200, Gerhard Dieringer wrote: > > 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. > > SELECT title,stopdate > FROM auction > WHERE stopdate = (SELECT max(stopdate) FROM auction); > > should work. Thanks for your suggestion. Yes this would work nicely but if I need to add more conditional clauses I have to duplicate them in the main SELECT and in the sub-SELECT: SELECT title,max(stopdate) FROM auction WHERE stopdate = (SELECT max(stopdate) FROM auction AND stopdate > now()) AND stopdate > now(); Or am I missing something? Tom Lane suggested using: SELECT title,stopdate FROM auction ORDER BY stopdate LIMIT 1; which seems the best solution (I was a bit concerned about performance, but then again the max() aggregate does a scan of all rows as well). Cheers, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr This is Linux Country. On a quiet night you can hear Windows NT reboot.
В списке pgsql-sql по дате отправления: