Re: group by function, make SQL cleaner? - Mailing list pgsql-sql

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