班级人数排序的问题，请前辈指导！

[salecn]

问题：想实现按班级人数降序排序(人数多的班级排前边，人数少的班级排后边，如下边数据应这样排序：Python1班、Python1班、Java1班、大数据1班)，请前辈指导一下！

数据内容如下：
学号 班级
1001 大数据1班
1002 Java1班
1003 Java1班
1004 Java1班
1005 Java1班
1006 Java1班
1007 Java1班
1008 Python1班
1009 Python1班
1010 Python1班
1011 Python1班
1012 Python1班
1013 Python1班
1014 Python1班
1015 Python1班
1016 Python1班
1017 Python2班
1022 Python2班
1023 Python2班
1024 Python2班
1025 Python2班

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL
	DROP TABLE t
GO
CREATE TABLE t(
	sno VARCHAR(10),
	cname NVARCHAR(20)	
)
GO
INSERT INTO t(sno,cname)
select '1001','大数据1班'
union all select '1002','Java1班'
union all select '1003','Java1班'
union all select '1004','Java1班'
union all select '1005','Java1班'
union all select '1006','Java1班'
union all select '1007','Java1班'
union all select '1008','Python1班'
union all select '1009','Python1班'
union all select '1010','Python1班'
union all select '1011','Python1班'
union all select '1012','Python1班'
union all select '1013','Python1班'
union all select '1014','Python1班'
union all select '1015','Python1班'
union all select '1016','Python1班'
union all select '1017','Python2班'
union all select '1022','Python2班'
union all select '1023','Python2班'
union all select '1024','Python2班'
union all select '1025','Python2班'
;WITH cte AS (
SELECT cname,COUNT(1) AS cnt FROM t GROUP BY cname
)
SELECT t.* FROM cte INNER JOIN t ON cte.cname=t.cname
ORDER BY cte.cnt DESC;
/*
sno        cname
---------- --------------------
1008       Python1班
1009       Python1班
1010       Python1班
1011       Python1班
1012       Python1班
1013       Python1班
1014       Python1班
1015       Python1班
1016       Python1班
1002       Java1班
1003       Java1班
1004       Java1班
1005       Java1班
1006       Java1班
1007       Java1班
1017       Python2班
1022       Python2班
1023       Python2班
1024       Python2班
1025       Python2班
1001       大数据1班
*/

[xbmu3]

楼上的答案就很好

[RINK_1]

select * 
from
(select *,COUNT(学号) over (partition by 班级) as 人数 from table_XXX) as A
order by 人数 desc,班级,学号

[salecn]

谢谢前辈指导！