Stillnet Studios

I’ve used this trick several times when needing to sort data into groups. For example say you need to sort car classified listings by price. Some of the dealers enter a price of “0″ for their listings. When sorting by lowest price, you want the listings that actually have a low price to come first, rather than than the cars costing $0 (hey, I’d like to buy your free car, thank you).

SELECT year, make, model, price,
tmpSortPriceField =
    CASE price
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

But I ran into a problem when some of the records had a NULL price. They were getting a tmpSortPriceField value of 1, because they hit the “ELSE 1″ part, thus were getting sorted up front. So I added a line in the CASE statement for the NULL values:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE price
        WHEN 0 THEN 2
        WHEN NULL THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

But that didn’t work, the records with a null price were still getting a tmpSortPriceField value of 1. I tried using IS NULL and that help either. I don’t know why but my null comparison just wasn’t working. I finally solved it by changing from the simple case form to the searched case form, where each entry in the case statement is compared for a boolean condition.

SELECT year, make, model, price,
tmpSortPriceField =
    CASE
        WHEN price = 0 THEN 2
        WHEN price IS NULL THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

I don’t know exactly why this works better, but it does seem to handle the NULLs appropriately. I’ve also heard from a nearby SQL guru that he always gets better results with the searched case statement, too.

Update - Mark Kruger suggested using IsNull may work as well. I tested it, and it does work, but only in SQL Server. So far the code above will work in MSSQL or MySQL. Using IsNull() will break in MySQL because the IsNull() function in MySQL is different than the one in MSSQL. But here is how you would do it using IsNull() in SQL Server:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE IsNull(price,0)
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

Update 2 - BUT, I believe using COALESCE() would work in either MySQL or MSSQL:

SELECT year, make, model, price,
tmpSortPriceField =
    CASE COALESCE(price,0)
        WHEN 0 THEN 2
        ELSE 1
    END
from cars ORDER BY tmpSortPriceField, price

This entry was posted on Wednesday, June 4th, 2008 at 2:05 pm and is filed under Web Development, SQL.