How to ORDER BY count of rows with specific column value

Question

I have a SQL select statement as follows:

select * from editortemp order by case 
when CategoryID = 10 then 0 
else CategoryID end,
Ranking

I would like to change the ordering of the result, so that in addition to putting all rows with CategoryID = 10 at the top, the REST of the rows will be ordered according to the count of the rows with each CategoryID, in descending order.

When I try doing

select * from editortemp order by case 
when CategoryID = 10 then 0 
else count(CategoryID) end,
Ranking

I get the error: Column 'editortemp.EditorTempID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

After researching this error online, it looks like this means there's a problem with the logic of the select statement.

How do I create a logical select statement which will order the rows as I described above?

EDIT: When I try to run the following statement:

 select * from editortemp order by case 
    when CategoryID = 10 then 0 
    else (select count(CategoryID) from editortemp) end,
    Ranking

I don't get the error mentioned above, but the ordering does not come out right. It puts all category=10 rows at the top, but then starts ordering according to the ranking, not according to the count of the categoryid... Any ideas what needs to be changed to get the results I want?

Thanks so much for your help!

Answer 1

You should really show a sample structure of your tables, some sample data and sample of what you are expecting in your original post.

From the way it looks, your editortemp table has multiple records and may have more data elements obviously not presented here. I would first get a pre-aggregate of counts, then join back to include as part of your ordering. Something like

select 
      et.* 
   from 
      editortemp
         JOIN ( select categoryID, count(*) as CatCount
                   from editortmp
                   group by categoryID ) preAgg
            on et.categoryID = preAgg.categoryID
   order by 
      case when CategoryID = 10 then 1 else 2 end,
      preAgg.CatCount desc,
      ranking

The case/when will force a preliminary sort of categoryID = 10 first, then anything else second. The secondary part of the order by is the count from the pre-aggregate table joined to. So, even if Category 10 has a count of 3 entries and Category 2 has 100, Category 10 still stays at the first spot... only then do the rest get sorted in descending order by count.

Per feedback...

I Don't know what the ranking is, but the ranking should only be an impact if there are multiple entries for a given category count.

What if categories 1, 2, 3, 4, 5 all have a count of 73 entries... and 
cat 1 has a ranks of 6, 12, 15...
cat 2 has ranks of 3, 20, 40...
cat 3 has ranks of 9, 10, 18... 
they will be intermingled.  

If you want all of same category grouped, then that would be added before ranking something like

   order by 
      case when CategoryID = 10 then 1 else 2 end,
      preAgg.CatCount desc,
      CategoryID,
      ranking

This way, inf the scenario of multiple categories having the count of 73, then the above order by will have all of category 1 by its ranking, then category 2 by its ranking, etc.