Conditional Order By on multiple columns

Question

I have the following query:

SELECT year, month, btype, bnumber 
FROM some_table 
ORDER BY year ASC, month ASC, bnumber ASC, btype ASC 

btype has the following values

block
week
month

When btype = block and bnumber = 1 I want it to be the first value in the table. But, when btype = block and bnumber = 2 I want it to be after all other bnumber = 2 values.

here is how the query is currently returning values:

year    month   btype   bnumber
======  ======  ======  ======
2017    3       block   1
2017    3       month   1
2017    3       week    1
2017    3       block   2
2017    3       week    2
2017    3       week    3
2017    3       week    4

This is how I would like the results to be:

year    month   btype   bnumber
======  ======  ======  ======
2017    3       block   1
2017    3       month   1
2017    3       week    1
2017    3       week    2
2017    3       block   2
2017    3       week    3
2017    3       week    4

Is something like this possible using CASE? I've tried several different variations but can't seem to get it to work. This is the last statement I tried to attempt to make btype = block and bnumber = 2 be treated as bnumber = 3 but it didn't work:

ORDER BY year ASC, month ASC, 
         CASE btype 
            WHEN 'block' and bnumber = '2' THEN bnumber = '3' 
            ELSE 1 
         END ASC, 
         bnumber ASC, 
         btype ASC

Answer 1

Something like this would satisfy the specification:

ORDER
   BY year
    , month
    , bnumber
    , CASE
        WHEN btype = 'block' AND bnumber > 1 
        THEN 'zzz'
        ELSE btype
      END

The CASE expression is usually going to return the value of btype, which is what it looks like we usually want to order on (alphabetically), after ordering on bnumber.

We handle the "special" case of btype='block' and bnumber>1 ... instead of returning btype, we return special a high value 'zzz' that is higher than all other value of btype.

---

A more robust solution would use a separate expression in the ORDR BY, before the btype column:

ORDER 
   BY year
    , month
    , bnumber
    , CASE
        WHEN btype = 'block' AND bnumber > 1 
        THEN 1
        ELSE 0
      END
    , btype

Answer 2

Try this:

SELECT year, month, btype, bnumber
FROM some_table
ORDER BY year ASC, month ASC, 
         CASE 
            -- Place btype ='block' / bnumber = '1' at the first place
            WHEN btype ='block' and bnumber = '1' THEN 1
            -- Prioritize bnumber = '2' / btype <> 'block' over 
            -- bnumber = '2' / btype = 'block'
            WHEN bnumber = '2' THEN CASE 
                                      WHEN btype <> 'block' THEN 1.1
                                      ELSE 1.2
                                    END            
            ELSE bnumber  
         END ASC, 
         bnumber ASC, 
         btype ASC

Demo here