Wow, that’s a mouthful. This is what I am trying to convey: When you do a Group By with Rollup, what happens is that the rolled up column shows null, and won’t necessarily be ordered at the bottom. For example:
select
firstname
, count(*) as total
from AdsEntered ads
join tblStaff s on s.staffid = ads.staffidgroup by firstname
with rollup
order by
total desc
This would output:
Now, I want the person with the highest total at the top, but I certainly don’t want the the total row being first. So the goal is to still sort descending, yet stick the total row at the bottom:
select
isnull(firstname, "TOTAL") as firstname
, count(*) as total
from AdsEntered ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end desc
This would yield:
First, I used isnull(firstname, “TOTAL”) to get rid of the ugly null. Pretty simple.
Now look at the case statement in the order by. I am saying, “when the firstname column is null, then treat it like it is 0, else treat it like it is 1. Then order it descending, so the field with the 1 will go last.” Notice I wrote when firstname is null, not when firstname = ‘TOTAL’. To check for ‘TOTAL’ you would have to apply the entire line isnull(firstname, “TOTAL”) = “TOTAL”.
Now that we got the total line in the right place, we need to order all the “0” rows descending. We do this by applying another order by on the total column.
select
isnull(firstname, "TOTAL") as firstname
, count(*) as total
from AdsEntered ads join tblStaff s on s.staffid = ads.staffid
group by firstname
with rollup
order by
case when firstname is null then 0
else 1 end desc
, total desc
Yielding:
Cool, huh?