CODEWITHSUNDEEP
mysqlsql-order-by
Booza
Booza

Reputation: 126

Order By priority not alphabetically

I am trying to prioritize my query by the ticket_priority_id.

The IDs are categorized as ASAP, HIGH, MED, LOW.

When I order by ticket_priority_id it always puts it in alphabetical order (LOW is ahead of MED).

How can I make it so I can order by ticket_priority_id but not alphabetically but by importance. I want the order to go (from top to bottom) ASAP - HIGH - MED - LOW

Upvotes: 4

Views: 710

Answers (2)

fthiella
fthiella

Reputation: 49049

You could use FIELD function:

ORDER BY
  FIELD(ticket_priority_id, 'ASAP', 'HIGH', 'MED', 'LOW')

Please notice that if ticket_priority_id is not present in the following list, it will return 0, so you might want to add also want this:

ORDER BY
  ticket_priority_id NOT IN ('ASAP', 'HIGH', 'MED', 'LOW'),
  FIELD(ticket_priority_id, 'ASAP', 'HIGH', 'MED', 'LOW')

to put rows that have no priority at the bottom instead of the top.

Upvotes: 5

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can use a case statement in your order by like this

ORDER BY 
   CASE WHEN ticket_priority_id = 'ASAP' THEN 1
        WHEN ticket_priority_id = 'HIGH' THEN 2
        WHEN ticket_priority_id = 'MED' THEN 3
        WHEN ticket_priority_id = 'LOW' THEN 4
        ELSE 5
   END

Upvotes: 8

Related Questions