MYSQL Specific Order With Conditionals

Okay… I have three columns that I’d like to order based on their conditions.

• Column 1 (INT) = last_reboot
• Column 2 (BOOL) = onlinecheck
• Column 3 (INT) = drive_use

I’d like “last_reboot” to be default and ordered descending. If “onlinecheck” = 0, I’d like it to be at the top of my query else ordered by the the “last_reboot” desc. Lastly, if “drive_use” is > than 90 I’d like it ordered above “last_reboot” and below “onlinecheck” = 0 else ordered by “last_reboot”.

EXAMPLE GOAL:

| last_reboot | onlinecheck | drive_use |
----------------------------------------------
|      15     |      0      |     80    |
|      90     |      1      |     91    |
|     320     |      1      |     80    |
|      45     |      1      |     50    | 
|      20     |      1      |     89    | 

I was able to achieve half of this by using the ORDER BY FIELDS but once I introduce “drive_use” I cannot get it to function properly. “drive_use” is always ordered above “last_reboot” if it’s NOT NULL.

SELECT * FROM <table> ORDER BY FIELD (onlinecheck, 0) DESC, (drive_per > 90), last_reboot DESC;

Any and all assistance greatly appreciated. Please let me know if further explanation is required.

Answer

I think you have to work with ORDER BY CASE: https://dev.mysql.com/doc/refman/8.0/en/case.html

I’m not sure I’m understanding the case well, but I think this comes quite close:

ORDER BY (CASE
    WHEN onlinecheck = 0 THEN 2 
    WHEN (drive_use > 90) THEN 1 
    ELSE 0
END) DESC,
last_reboot DESC

Here a DB-Fiddle