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

Check for IS NULL instead of '' and see if it makes a difference. — Emmie Lewis-Briggman
– Emmie Lewis-Briggman, Commented May 29, 2012 at 4:25

acattle · Accepted Answer · 2012-05-29 07:01:13Z

6

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.

edited May 29, 2012 at 7:01

answered May 29, 2012 at 4:31

acattle

3,1131 gold badge18 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

acattle Over a year ago

In an earlier version of this post I mistakenly said that the reason for NULL values in DBs was to save memory. It was brought to my attention that this is incorrect and I have updated my answer to reflect this.

acattle · Accepted Answer · 2012-05-29 10:04:01Z

3

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

edited May 29, 2012 at 10:04

acattle

3,1131 gold badge18 silver badges21 bronze badges

answered May 29, 2012 at 7:58

Sudhanshu Jain

821 silver badge6 bronze badges

1 Comment

acattle Over a year ago

+1 because I'd honestly never heard of COALESCE before this post.

Collectives™ on Stack Overflow

Writing a sorting query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related