Using Case in Order By with Group By

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:

Groupbynull

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:

Orderbycase1

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:

Orderbycase

Cool, huh?

Leave a Reply

Your email address will not be published. Required fields are marked *