Manual order in SQL

Question

If I have a table of things...

ThingName | ThingCategory
-------------------------
thing 1   | cat1
thing 2   | cat2
thing 3   | cat3

How would I select them ordered by first cat2, then cat1, then cat3

Is this even possible?

Answer 1

A switch in the order clause is one way to get you there with code alone:

select *
from Things
order by case
    when ThingCategory = 'cat2' then 1
    when ThingCategory = 'cat1' then 2
    when ThingCategory = 'cat3' then 3
    else 4 -- Might want the default case, too
end

However, this type of functionality is often better handled by data rather than code. As in, if you had a ThingCategories table, that table could have an Ordinal integer column. At that point, you can simply have the following query:

select t.*
from Things t
    join ThingCategories c on t.ThingCategory = c.ThingCategory
order by c.Ordinal

This way you won't have to maintain literals in code (good luck with that), and the users can change ordering by simply updating ordinals in a table (no need to bother you).

Answer 2

You can do it like this

SELECT ThingName , ThingCategory
    FROM yourtabe
    ORDER BY 
     CASE
        WHEN ThingCategory = 'cat2' THEN 1
        WHEN ThingCategory = 'cat1' THEN 2
        WHEN ThingCategory = 'cat3' THEN 3
    END