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 e5GAstI53612 for ; Fri, 16 Jun 2000 06:54:55 -0400 (EDT) Received: by styx.apartia.int (Postfix, from userid 1000) id E01C151C3; Fri, 16 Jun 2000 12:54:53 +0200 (CEST) Date: Fri, 16 Jun 2000 12:54:53 +0200 From: Louis-David Mitterrand To: Gerhard Dieringer Cc: "<" Subject: Re: Antw: using max() aggregate Message-ID: <20000616125453.A501@styx> Reply-To: ldm@apartia.ch Mail-Followup-To: Gerhard Dieringer , "<" 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 10:56:04AM +0200 X-Archive-Number: 200006/165 On Fri, Jun 16, 2000 at 10:56:04AM +0200, Gerhard Dieringer wrote: > > 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,stopdate > > FROM auction > > WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE 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). > > ... > > I don't see why you repeat your conditions in the outer select. The > condition in the inner select drops all records that violate the > conditions, so the same conditions in the outer select have nothing to > do and you can leave them away. Maybe mine was a bad example but if, for instance, you add a condition on the "login" attribute (that it should start with a 'm'), then if you omit the clause from the outer select you risk having a false match if two records have the same stopdate: SELECT title,login,stopdate FROM auction WHERE stopdate = (SELECT max(stopdate) FROM auction WHERE login LIKE 'm%'); > Tom's solution has the drawback, that if you have more than one record > with the same max value you only get one of them, but may be that you > want to see all of them. True. Thanks, -- Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr Save the whales. Feed the hungry. Free the mallocs.