How to count the number of rows with a specific ORDER BY column value
I have a select SQL statement like this:
select * from editortemp order by case
when CategoryID = 10 then 0
else CategoryID end,
Ranking
I would like to reorder the result so that besides placing all the rows with CategoryID = 10 at the top, the REST rows will be ordered according to the number of rows with each category ID, in descending order.
When I try to do
select * from editortemp order by case
when CategoryID = 10 then 0
else count(CategoryID) end,
Ranking
I am getting the error: The column "editortemp.EditorTempID" is not valid in the select list because it is not contained in either the aggregate function or the GROUP BY clause.
After investigating this error online, it looks like it means there is a problem with the logic of the select statement.
How do I create a boolean selection operator that will order the rows as 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 am not getting the error mentioned above, but the order is not getting correctly. It puts all categories = 10 rows at the top, but then starts ordering according to ranking, not category count. Any ideas what needs to be changed to get the results I want?
Many thanks for your help!
source to share
You should really show an example of the structure of your tables, some sample data and a sample of what you are expecting in your original post.
From the way it looks, your editortemp table has multiple entries and may contain more data items not explicitly presented here. I first have to get the preliminary count and then join it to include it as part of your order. 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
In the case / when at first the type categoryID = 10 will be forcibly assigned first, then something else second. The secondary part of the order is the invoice from the table with the preliminary aggregate. So even if category 10 has a score of 3 entries, and category 2 has 100, category 10 still stays in first place ... only then the rest will be sorted in descending order by score.
For the tip ...
I don't know what a ranking is, but a ranking should only be the result if there are multiple entries to count for a given category.
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 the same categories to be grouped then this will be added before ranking something like
order by
case when CategoryID = 10 then 1 else 2 end,
preAgg.CatCount desc,
CategoryID,
ranking
So in a scenario of multiple categories having a score of 73, then the above order would have all category 1 by its ranking, then by category 2 by its rank, etc.
source to share
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
source to share
You can do what you want:
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 sentence puts CategoryId
10 at the top. The second class sorts the rest by the number of category IDs. The third column is Ranking
used Ranking
.
source to share