CODEWITHSUNDEEP
phpmysqlsql
aWebDeveloper
aWebDeveloper

Reputation: 38342

Mysql complex order by

I have 3 columns in my table

  1. status enum('Completed','Incomplete','In Progress')
  2. severity enum('High','Low','Moderate')
  3. created datetime

Now currently i am ordering by severity ASC created DESC status DESC. which is not working right. This is how i want it to be listed.

  1. Incomplete - High
  2. Incomplete - moderate
  3. Incomplete - low
  4. In Progress - High
  5. In Progress - moderate
  6. In Progress - low
  7. Completed - High
  8. Completed - moderate
  9. Completed - low

Upvotes: 1

Views: 270

Answers (3)

Rafael
Rafael

Reputation: 356

You can store Status and Severity as numbers, the use:

SELECT ... FROM ... ... ORDER BY STATUS [ASC/DESC], SEVERITY [ASC/DESC]

ASC or DESC will depend on wich numbers you choose.

To have severity and status as text you can use a IF/Switch or a INNER JOIN if you want more Status/Severity

Upvotes: 0

gbn
gbn

Reputation: 432190

As well as Andomar's solution, I'd consider having proper tables for status and severity. Not enums.

You can imply sort order from the key of these tables, but I'd probably have a "SortOrder" column for future use.Then you can JOIN the tables and order by the SortOrder.

No need repeat the CASE in every query that needs it

Edit: Simplifying Andomar's idea...

order by
    case status
       when 'Incomplete' then 1
       when 'In Progress' then 2
       when 'Completed' then 3
    END,
    case severity
       when 'High' then 1
       when 'Moderate' then 2
       when 'Low' then 3
    END

Upvotes: 4

Andomar
Andomar

Reputation: 238048

You can use a case to order rows like that:

select  *
from    YourTable
order by
        case 
        when status = 'Incomplete' and severity = 'High' then 1
        when status = 'Incomplete' and severity = 'Moderate' then 2
        ...
        end

Upvotes: 5

Related Questions