Manual order in SQL

3
votes

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?

3
mysqlsql
Are you asking for a way to manually order the data? If so, then ORDER BY ThingCategory = "cat2" DESC. If you're asking for a more "general" case, then no, because there is no way to logically describe a general ordering like you're asking. – ean5533

3 Answers

9
votes

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).

2
votes

It is entirely possible. Just order by a comparison:

ORDER BY
  ThingCategory = "cat2" DESC,
  ThingCategory = "cat1" DESC,
  ThingCategory = "cat3" DESC
1
votes

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