问个查询后排序的SQL话语

问个查询后排序的SQL语句
能不能单纯通过SQL实现...
main表 两个字段id、team
能不能按team中某数据出现次数由高到低排序并列出出现的次数

例如

id | team | count
-----------------------
2 |team2 | 10
1 |team1 | 8
3 |team3 | 5

[解决办法]
晕，mysql你来SQLServer专区发干嘛？又不说清楚
[解决办法]

SQL code

drop table maincreate table main(id int identity,team varchar(10))goinsert into main select 'term1'GO 3insert into main select 'term2'GO 4insert into main select 'term3'GO 5--select id,team,(select count(1) [count] from main b where a.team=b.team group by team )[count]from main aorder by [count] desc