From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: group by function, make SQL cleaner?
Date: Mon, 03 Apr 2006 14:58:52 -0700
Message-ID: <44319A9C.9040800@obviously.com> (raw)
In-Reply-To: <4419A426.3030000@obviously.com>
References: <4418F52F.4070804@obviously.com>
<12933.1142489098@sss.pgh.pa.us>
<4419A426.3030000@obviously.com>
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/
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce1@obviously.com
Subject: Re: group by function, make SQL cleaner?
In-Reply-To: <44319A9C.9040800@obviously.com>
* 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