SQL Query: Need order by count, most must be on to

问题:

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

EXPECTED RESULTS:

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

Those with the SAME Zip should be ON top, then the rest follows. ORDER BY Zip does not work because it DOES sort by ZIP, and NOT by number of occurence

Using SQL Server 08

回答1:

SQL Server 2008 using COUNT() OVER

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

If you don't need to see the additional column, then you can move the COUNT() OVER clause into the ORDER BY clause.

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

回答2:

To accomplish this, you should join against a subquery which returns the count per zipcode. The joined subquery is only needed to provide the counts (even if not displayed), while the main table yourtable provides all the rest of the columns.

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

回答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