CODEWITHSUNDEEP
sqlsql-server-2012sql-order-by
code2b
code2b

Reputation: 142

How to ORDER BY count of rows with specific column value

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!

Upvotes: 4

Views: 3590

Answers (3)

DRapp
DRapp

Reputation: 48139

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.

Upvotes: 1

Sandeep
Sandeep

Reputation: 1210

select 
   TEMP1.* 
from 
     (
      select CategoryID, 999999999 AS Ranking FROM editortemp WHERE CategoryID = 10
      UNION ALL
      Select CategoryID, (SELECT COUNT(*) FROM editortemp AS t1 WHERE t1.CategoryID = t2.CategoryID) AS Ranking FROM editortemp AS t2 WHERE CategoryID <> 10
      ) TEMP1
ORDER BY
    TEMP1.Ranking DESC

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269663

You can do what you want as:

order by (case when CategoryID = 10 then 0 else 1 end),
         (case when CategoryID <> 10
               then (select count(*) from editortemp et2 where et2.CategoryID = editortemp.CategoryId)
          end),
         Ranking

The first clause puts CategoryId 10 at the top. The second sorts the rest by the number of category ids. The third uses Ranking as the final sort column.

Upvotes: 0

Related Questions