Received: from styx.apartia.int (postfix@r34m39.cybercable.tm.fr [195.132.34.39]) by hub.org (8.10.1/8.10.1) with ESMTP id e5G8BOI23956 for ; Fri, 16 Jun 2000 04:11:24 -0400 (EDT) Received: by styx.apartia.int (Postfix, from userid 1000) id 60B20500C; Fri, 16 Jun 2000 10:11:21 +0200 (CEST) Date: Fri, 16 Jun 2000 10:11:21 +0200 From: Louis-David Mitterrand To: Gerhard Dieringer Cc: pgsql-sql@postgresql.org Subject: Re: Antw: using max() aggregate Message-ID: <20000616101121.B6451@styx> Reply-To: ldm@apartia.ch Mail-Followup-To: Gerhard Dieringer , pgsql-sql@postgresql.org References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2i In-Reply-To: ; from DieringG@eba-haus.de on Fri, Jun 16, 2000 at 09:28:27AM +0200 X-Archive-Number: 200006/163 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.