Tom Lane wrote:<br /><blockquote cite="mid12933.1142489098@sss.pgh.pa.us" type="cite"><pre wrap="">In this particular
caseyou 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.
</pre></blockquote> Thanks. Markus Bertheau also supplied this solution:<br /><pre wrap="">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
It brings up a question though: is there any way in Postgres to set a "quirks" or "standards" mode. Or get Postgres to
logthe compliance level of each command and command element used, e,g.:
</pre><table align="center" border="1" cellpadding="2" cellspacing="2" width="40%"><tbody><tr><td valign="top">ORDER BY
n<br/></td><td valign="top">SQL92<br /></td></tr><tr><td valign="top">GROUP BY n<br /></td><td valign="top">PSQL<br
/></td></tr><tr><tdvalign="top">(SELECT ...)<br /></td><td valign="top">SQL99<br /></td></tr><tr><td
valign="top">SELECT<br/></td><td valign="top">SQL99<br /></td></tr><tr><td valign="top">count(...)<br /></td><td
valign="top">SQL99<br/></td></tr><tr><td valign="top">date_trunc(string,...)<br /></td><td valign="top">PQSL<br
/></td></tr></tbody></table><prewrap="">It is so easy to get lazy and start shrink-wrapping code to the database.
That'scertainly why mysql SQL tends to be so non-portable....
</pre>