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/ ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org