Thread: Possible parsing problem?

The following does NOT produce an error on the 'order by' clause,
though perhaps it should?

    SELECT col, count(*) from table group by col order by count;

If there is more than one count() function in the query, it does crete
am error for the ambiguous reference.

(FWIW, Oracle and MySQL both generate an error.)
--
Mike Nolan

Try giving the "count" items names.  I just ran the
following with good results:

select fy, yrmo, count(pt_id) as cases,
count(er_level) as er_cases from case_fy2003 group by
fy, yrmo order by cases desc;

Does this address your issue?

Andrew Gould

--- nolan@celery.tssi.com wrote:
> The following does NOT produce an error on the
> 'order by' clause,
> though perhaps it should?
>
>     SELECT col, count(*) from table group by col
> order by count;
>
> If there is more than one count() function in the
> query, it does crete
> am error for the ambiguous reference.
>
> (FWIW, Oracle and MySQL both generate an error.)
> --
> Mike Nolan
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>
http://www.postgresql.org/docs/faqs/FAQ.html

nolan@celery.tssi.com writes:
> The following does NOT produce an error on the 'order by' clause,
> though perhaps it should?

>     SELECT col, count(*) from table group by col order by count;

AFAICS that is 100% correct per SQL92, given that we assign the default
name of "count" to the second result column.

> (FWIW, Oracle and MySQL both generate an error.)

They probably assign some other name to the column.  The spec leaves it
to the implementation what name to assign to output columns, except in
very trivial cases (or when you force the issue with AS).

            regards, tom lane