I'm not sure why there is a reason for such behaviour.
For this table:
create table bg(id serial primary key, t text);
This works:
select count(id) from bg;
This works:
select count(distinct id) from bg;
And this doesn't:
select count(distinct id) from bg order by id;
ERROR: column "bg.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select count(distinct id) from bg order by id;
There is no "id" column in the returned dataset to order by. You are just returning one value, how would it be ordered? (and that row has a column named "count" - but you can alias it to SELECT count(distinct id) AS id FROM bg ORDER BY id - it just makes no sense to order a single row..
Oh, right. Thanks. I haven't noticed that there is no id column in the dataset.
