Re: Antw: using max() aggregate
От | Louis-David Mitterrand |
---|---|
Тема | Re: Antw: using max() aggregate |
Дата | |
Msg-id | 20000616125453.A501@styx обсуждение исходный текст |
Ответ на | Re: Antw: using max() aggregate ("Gerhard Dieringer" <DieringG@eba-haus.de>) |
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: