Re: group by function, make SQL cleaner?

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/