From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: group by function, make SQL cleaner?
Date: Thu, 16 Mar 2006 01:04:58 -0500
Message-ID: <12933.1142489098@sss.pgh.pa.us> (raw)
In-Reply-To: <4418F52F.4070804@obviously.com>
References: <4418F52F.4070804@obviously.com>
Bryce Nesbitt <bryce1@obviously.com> writes:
> SELECT date_trunc('day',endtime),count(*)
> FROM eg_event where endtime >= '2006-02-01' and endtime < '2006-03-01'
> GROUP BY date_trunc('day',endtime)
> ORDER BY date_trunc('day',endtime);
> Is there a way to eliminate the ugly repeated use of
> date_trunc('day',endtime)?
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.
This does not work in any context except repeating a SELECT result
expression in GROUP BY or ORDER BY.
regards, tom lane
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: tgl@sss.pgh.pa.us, bryce1@obviously.com
Subject: Re: group by function, make SQL cleaner?
In-Reply-To: <12933.1142489098@sss.pgh.pa.us>
* 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