Skip site navigation (1) Skip section navigation (2)

Re: having clause question

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ian Barwick <barwick(at)gmail(dot)com>
Cc: Shane Wegner <shane-keyword-pgsql(dot)a1e0d9(at)cm(dot)nu>,pgsql-general(at)postgresql(dot)org
Subject: Re: having clause question
Date: 2004-10-30 23:12:21
Message-ID: 22896.1099177941@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Ian Barwick <barwick(at)gmail(dot)com> writes:
> On Sat, 30 Oct 2004 15:17:16 -0700, Shane Wegner
> <shane-keyword-pgsql(dot)a1e0d9(at)cm(dot)nu> wrote:
>> Under MySQL, this query does the trick.
>> select lastname,count(*) as c from names group by lastname
>> having c > 1;
>> 
>> Is it possible to do a query like this with PostgreSQL?

> select lastname,count(*) as c from names group by lastname having count(*) > 1;

Just to expand on that: MySQL's abbrevation is not legal SQL, and it's
not even very sensible, because logically speaking the SELECT output
list is only evaluated after (and if) the HAVING clause succeeds.
So it makes no sense for the HAVING clause to refer to SELECT values.

Postgres does optimize the case of multiple identical aggregate-function
invocations in a query, BTW, so the apparent inefficiency is not real.

			regards, tom lane

In response to

pgsql-general by date

Next:From: Ken TozierDate: 2004-10-31 00:35:50
Subject: Superuser log-in through a web interface?
Previous:From: Ian BarwickDate: 2004-10-30 22:36:47
Subject: Re: having clause question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group