Writing a sorting query

Question

I have a table as below:

Job    Quantity Status
-----------------------
1      100      OK
2      400      HOLD
3      200      HOLD
4      450      OK
5      300     
6      500      

I would like my result to be shown as below:

Job  Quantity Status
----------------------
4      450      OK
2      400      HOLD
1      100      OK
3      200      HOLD
6      500 
5      300

I have created this query but it's not working when the table contains data where the status column is null/empty

SELECT 
    Job, 
    Quantity, 
    Status 
FROM 
    myTable
ORDER BY CASE 
    WHEN QUANTITY >= 400 AND STATUS = 'OK' THEN 1 
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD' THEN 2 
    WHEN QUANTITY <  400 AND STATUS = 'OK' THEN 3 
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD' THEN 4 
    WHEN QUANTITY >= 400 AND STATUS = '' THEN 5 
    WHEN QUANTITY <  400 AND STATUS = '' THEN 6 
END

Answer 1

Try using the following query:-

SELECT  
    Job,  
    Quantity,  
    Status  
FROM  
    myTable 
ORDER BY CASE  
    WHEN QUANTITY >= 400 AND STATUS = 'OK' THEN 1  
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD' THEN 2  
    WHEN QUANTITY <  400 AND STATUS = 'OK' THEN 3  
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD' THEN 4  
    WHEN QUANTITY >= 400 AND COALESCE(STATUS,'') = '' THEN 5  
    WHEN QUANTITY <  400 AND COALESCE(STATUS,'') = '' THEN 6  
END 

The COALESCE() function returns the first non-null argument so if STATUS is NULL it will return ''.

http://www.sqlbook.com/SQL/SQL-Coalesce-28.aspx

Answer 2

Unless I'm misreading, I feel like you've answered your own question. You need to account for when your data is NULL.

The important part is that NULL is of a different type than ''. Just like '' = 0 is false, so is NULL = ''. NULL is used to represent vacuous values. Think about it in terms of boolean values instead of string. Obviously there are times where something is neither true nor false, this is where a value like NULL would come in. Similarly, if you think of strings as pure data instead of characters and words then there is a difference between the empty value and no value at all.

For more information, see http://www.w3schools.com/sql/sql_null_values.asp

So I think your code should look like:

SELECT Job, Quantity, Status 
    FROM myTable
ORDER BY CASE 
    WHEN QUANTITY >= 400 AND STATUS = 'OK' THEN 1 
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD'  THEN 2 
    WHEN QUANTITY < 400 AND STATUS = 'OK'  THEN 3 
    WHEN QUANTITY >= 400 AND STATUS = 'HOLD'  THEN 4 
    WHEN QUANTITY >= 400 AND (STATUS = '' OR STATUS IS NULL)  THEN 5 
    WHEN QUANTITY < 400 AND (STATUS = '' OR STATUS IS NULL)  THEN 6
END

Unless you explictly set the STATUS to '', you might be able to just use STATUS IS NULL.