Message-ID: <44319A9C.9040800@obviously.com>
Date: Mon, 03 Apr 2006 14:58:52 -0700
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.12) Gecko/20050921
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: group by function, make SQL cleaner?
References: <4418F52F.4070804@obviously.com> <12933.1142489098@sss.pgh.pa.us>
	<4419A426.3030000@obviously.com>
In-Reply-To: <4419A426.3030000@obviously.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Bryce Nesbitt wrote:
> Tom Lane wrote:
>> In this particular case you could say
>>
>> 	... GROUP BY 1 ORDER BY 1;
>>
>> "ORDER BY n" as a reference to the n'th SELECT output column is in the
>> SQL92 spec.  (IIRC they removed it in SQL99, but we still support it,
>> and I think most other DBMSes do too.)  "GROUP BY n" is *not* in any
>> version of the spec but we allow it anyway.  I'm not sure how common
>> that notation is.
>>
>>   
> Thanks.  Markus Bertheau also supplied this solution:
> SELECT enddate, count(*) FROM (
>     SELECT date_trunc('day', endtime) AS enddate FROM eg_event WHERE
> endtime >= '2006-01-01'  and endtime < '2006-03-01') as foo
> GROUP BY enddate
> ORDER BY enddate
>   

Hmm.  Is there a way to specify the "n" column in a WHERE?


demo=> select p_last_name,count(*) from xx_person group by p_last_name
where 2  > 28;
ERROR:  syntax error at or near "where" at character 65
LINE 1: ...name,count(*) from eg_person group by p_last_name where '3' ...


demo=> select p_last_name,count(*) from xx_person group by p_last_name
order by 2 desc limit 6;
p_last_name | count
-------------+-------
Smith | 44
Miller | 37
Lee | 35
Williams | 33
Johnson | 30
Jones | 28
(6 rows)


-- 
----
Visit http://www.obviously.com/