Apologies for raising this - I've found a message in the
archives from about a month ago; but...
I can't find the relevant sections in the SQL spec; nevertheless, the
docs for the SELECT command lead me to believe that this should work,
since:
http://developer.postgresql.org/docs/postgres/sql-select.html
[[
In the SQL-92 standard, an ORDER BY clause may only use result column
names or numbers, while a GROUP BY clause may only use expressions based
on input column names. PostgreSQL extends each of these clauses to allow
the other choice as well (but it uses the standard's interpretation if
there is ambiguity). PostgreSQL also allows both clauses to specify
arbitrary expressions. Note that names appearing in an expression will
always be taken as input-column names, not as result-column names.
]]
Sure enough:
select col1 as x, col2 as y from table1 order by x;
...works, as does:
select col1 as x, count(col2) as y from table1
group by x
having count(col2) = 1;
"Having" operates after "group by"; and I'd argue that
[[
Each column referenced in condition must unambiguously reference a
grouping column, unless the reference appears within an aggregate
function.
]]
so since we can "group by x", one would imagine that
select col1 as x, count(col2) as y from table1
group by x
having x = 1;
should work - it's hardly ambiguous.
I appreciate the distinction between input and output columns;
nevertheless, the loosening of the behaviour of "group by" doesn't seem
to sit with the restriction on "having". I also appreciate the need to
support "standard" sql - but in the absence of ambiguities, shouldn't
this expression "do what I mean"?
Cheers,
jan
--
jan grant, ILRT, University of Bristol. http://www.ilrt.bris.ac.uk/
Tel +44(0)117 9287864 or +44 (0)117 9287088 http://ioctl.org/jan/
You see what happens when you have fun with a stranger in the Alps?