From: Gerhard Dieringer <DieringG@eba-haus.de>
To: cunctator@apartia.ch
Cc: " <"<pgsql-sql@postgresql.org>
Subject: Re: Antw: using max() aggregate
Date: Fri, 16 Jun 2000 10:56:04 +0200
Message-ID: <s94a07de.061@kopo001> (raw)
Louis-David Mitterrand - ldm@apartia.org - http://www.apartia.fr 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,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).
> ...
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.
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.
Gerhard
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: DieringG@eba-haus.de, cunctator@apartia.ch, "<pgsql-sql@postgresql.org
Subject: Re: using max() aggregate
In-Reply-To: <s94a07de.061@kopo001>
References: <s94a07de.061@kopo001>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox