How to keep mysql names together in ordered list-CodePudding

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!

CodePudding user response：

If you want the NULL values right after Prerequisites, you can use PrerequisitesZ as the fallback for NULL values:

ORDER BY COALESCE(Category, 'PrerequisitesZ') DESC, OrderNum;

CodePudding user response：

You can try using two conditions in the ORDER BY clause:

if Category has the value "Prerequisites", it should come first
otherwise order by the OrderNum value

Here's how you would do it:

SELECT * 
FROM tab
ORDER BY IF(Category='Prerequisites', 0, 1), 
         OrderNum

Demo here.

Note: Actually you can play how much you want with the conditions in the ORDER BY clause, as it can accept most of MySQL constructs.

CodePudding user response：

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.