SQL查询：计数需要秩序，最必须在最前面，其余如下(SQL Query: Need order by

TABLEA

JobCode Job1 Job2 Job3 zip
------- ---- ---- ---- ----------
F       F    S    NULL 90030
F       F    S    NULL 90031
F       F    S    NULL 90031
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90034
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90035
F       F         NULL 90035-4640

预期成绩：

JobCode Job1 Job2 Job3 zip
------- ---- ---- ---- ----------
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90034
F       F    S    NULL 90034
F       F         NULL 90034
F       F    S    NULL 90031
F       F    S    NULL 90031
F       F    S    NULL 90030
F       F    S    NULL 90035
F       F         NULL 90035-4640

那些具有相同邮编应该是放在上面，那么剩下如下。 ORDER BY邮编不起作用，因为它通过ZIP排序呢，而不是由occurence数

使用SQL Server 08

Answer 1:

SQL Server 2008中使用COUNT() OVER

select *, c = count(1) over (partition by zip)
from tbl
order by c desc;

如果你不需要看其他列，那么你可以移动COUNT() OVER子句为ORDER BY子句。

select JobCode, Job1, Job2, Job3, zip
from tbl
order by count(1) over (partition by zip) desc;

Answer 2:

要做到这一点，你应该加入反对返回每邮编计数的子查询。 在加入子查询只需要提供计数（即使未显示），而主表yourtable提供列的所有的休息。

SELECT 
  JobCode, 
  Job1,
  Job2,
  Job3,
  subq.zip
FROM
  yourtable
  JOIN (
     /* Subquery returns count per zip group */
     SELECT zip, COUNT(*) AS numzip 
     FROM yourtable 
     GROUP BY zip
  ) subq ON yourtable.zip = subq.zip
ORDER BY numzip DESC

Answer 3:

SELECT 
  JobCode, Job1, Job2, Job3, order_jobs.zip
FROM
  jobs
  JOIN (SELECT zip, COUNT(*) AS zipcount FROM jobs GROUP BY zip) ordering 
ON jobs.zip = ordering.zip
ORDER BY zipcount DESC

文章来源: SQL Query: Need order by count, most must be on top, the rest follows