SQL Query: you need an order in the account, most of them should be on top, the rest follows

Question

Tablea

JobCode Job1 Job2 Job3 zip ------- ---- ---- ---- ---------- FFS NULL 90030 FFS NULL 90031 FFS NULL 90031 FFS NULL 90034 FF NULL 90034 FFS NULL 90034 FFS NULL 90034 FF NULL 90034 FFS NULL 90035 FF NULL 90035-4640 

EXPECTED RESULTS:

 JobCode Job1 Job2 Job3 zip ------- ---- ---- ---- ---------- FFS NULL 90034 FF NULL 90034 FFS NULL 90034 FFS NULL 90034 FF NULL 90034 FFS NULL 90031 FFS NULL 90031 FFS NULL 90030 FFS NULL 90035 FF NULL 90035-4640 

Those with a SAME Zip should be at the top, and then the rest. ORDER BY Zip does not work, because it is sorted by ZIP, and NOT by the number of cases

Using SQL Server 08

Answer 1

SQL Server 2008 using COUNT() OVER

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

If you do not need to see an additional column, you can move the COUNT() OVER clause to the ORDER BY clause.

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

Answer 2

To accomplish this, you must join a subquery that returns an invoice for zipcode. The combined subquery is only needed for counting (even if it is not displayed), while the main table yourtable provides all the other 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 

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