Re: group by function, make SQL cleaner?

Bryce Nesbitt <bryce1(at)obviously(dot)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