CODEWITHSUNDEEP
sql-server-2005
MrVimes
MrVimes

Reputation: 3312

How to order by a specific order using a field determined by a case statement

I have the following query (simplified version for the question)

select case when pool_type in ('WN','PL','SH','EX','QN') 
  then pool_type else 'Other' end as pooltype,
sum(wagered)
from wageringactivitytable
group by case when pool_type in ('WN','PL','SH','EX','QN') 
then pool_type else 'Other' end

I wish to order the results in a specific order (WN,PL,SH,EX,QN,Other) Is this even possible given the way I've constructed my sql query?

I tried using another case statement (with numbers such as 'case pool_type when 'WN' then 1 when 'PL' then 2 etc..) but that won't work because pool_type is not valid in the order by clause because it is neither contained in an agregate function or a group by clause....

I tried refering to the column position rather than it's name, but in this case the order is completely ignored..

case 1 when 'WN' then 1
when 'PL'then 2
....

when 'Other' then 6

end

Upvotes: 0

Views: 26

Answers (1)

Hogan
Hogan

Reputation: 70523

One way to do this is with an outer query like this:

SELECT *
FROM (
    select case when pool_type in ('WN','PL','SH','EX','QN') 
      then pool_type else 'Other' end as pooltype,
    sum(wagered)
    from wageringactivitytable
    group by case when pool_type in ('WN','PL','SH','EX','QN') 
    then pool_type else 'Other' end
) T
ORDER BY
  CASE WHEN pooltype = 'WN' THEN 1 
       WHEN ...
  END

Another way to do this is make a helper table... eg

pooltypeorderer
pooltype poolorder
WN       1
PL       2
etc

Then you join to that table and group by both pool_type and pool_order

I like this second option because it is easy to maintain, you can change the order (or add new types) without changing your code. It does make the query look a little more complex (since it has an extra join).

Upvotes: 1

Related Questions