How to keep mysql names together in ordered list

Question

I have the following list of items

Category	OrderNum
Prerequisites	2
NULL	4
Prerequisites	6
Sign Off	8
Sign Off	10

I would like it to be ordered so that 'Prerequisites' is together and the NULL category appears after it, so that:

Category	OrderNum
Prerequisites	2
Prerequisites	6
NULL	4
Sign Off	8
Sign Off	10

Currently my SQL has the following order by:

ORDER BY OrderNum <> '' DESC, OrderNum

I've tried the following, however it puts NULL at the end.

ORDER BY COALESCE(Category,'') <> '' DESC, OrderNum <> '' DESC, OrderNum

I'm trying to achieve it so that the records with the same category are together in the recordset, the NULL item should appear before the 'Sign Off' category because the OrderNum of NULL is less than any of the 'Sign Off' records.

I'm not sure if that's possible in one query. Any help would be appreciated.

Thanks!

Answer 1

You can apply all the conditions that you want with a CASE expression:

SELECT * FROM tablename
ORDER BY CASE 
           WHEN Category = 'Prerequisites' THEN 1
           WHEN Category IS NULL THEN 2
           ELSE 3
         END, 
         Category,
         OrderNum;

or, if there are also empty strings in Category which you want sorted with the NULLs:

SELECT * FROM tablename
ORDER BY CASE 
           WHEN Category = 'Prerequisites' THEN 1
           WHEN COALESCE(Category, '') = '' THEN 2
           ELSE 3
         END, 
         Category,
         OrderNum;

or:

SELECT * FROM tablename
ORDER BY Category = 'Prerequisites' DESC, 
         Category IS NULL DESC, -- or: COALESCE(Category, '') = '' DESC,
         Category,
         OrderNum;

But, if what you want is to sort the rows by the minimum OrderNum of each Category use a correlated subquery:

SELECT t1.* FROM tablename t1
ORDER BY (SELECT MIN(t2.OrderNum) FROM tablename t2 WHERE t2.Category = t1.Category),
         t1.OrderNum;

or, for MySql 8.0+ use MIN() window function:

SELECT * FROM tablename
ORDER BY MIN(OrderNum) OVER (PARTITION BY Category),
         OrderNum;

See the demo.