聚合函数的order by 问题

[aiphar]

以下语句提示order by 有问题：

select count(deportment.name) as name_num
from deportment
order by name_num;

请问对于类似对聚合函数进行排序应如何写？

[ACMAIN_CHM]

select count(deportment.name) as name_num
from deportment
order by 1;

[aiphar]

谢谢！

[liruilingfly]

2楼的有问题吧
select count(deportment.name) as name_num
from deportment
order by 1;
你这里就只有1条记录，1条记录order by就没意义了
那么你如果写错了，你可能想写成
select aaa,count(deportment.name) as name_num
from deportment
group by aaa
order by 2
那么告诉你，这个也是错的

所以如果对上面第二种情况进行排序的话，该
select t2.aaa,t2.name_num
(
select t1.aaa,count(deportment.name) as name_num
from deportment t1
group by aaa) t2
order by t2.name_num desc

[liruilingfly]

说错了，应该是1楼的，我上面的是一种方法，如果你不想嵌套结果集，可以用下面这种
select aaa,count(deportment.name) as name_num,
rank() over(order by count(count(deportment.name) desc)
from deportment
group by aaa