CODEWITHSUNDEEP
mysqlsqlsortingsql-order-by
Ryan Griggs
Ryan Griggs

Reputation: 2758

MySQL: how to sort some records in ASC and some in DESC order, based on another field's value

I have a table of "tickets".
Table structure:

  • unique ID (auto-increment)
  • status (open/closed/exception)
  • priority (numeric, larger number = higher priority)
  • creation_date
  • etc...

I need to retrieve the tickets in the following order:

  • "open" tickets first, sorted by priority (highest first), then by creation_date (oldest first)
  • "closed" tickets next, sorted by creation_date (newest first)

This could be done with a UNION of two queries, but this would add a lot of complexity.

Any suggestions for accomplishing this in a single query?

Upvotes: 0

Views: 83

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Don't use union all. Use multiple keys in the order by:

select t.*
from t
where status in ('open', 'closed')
order by (status = 'open') desc,
         (case when status = 'open' then priority end) desc,
         (case when status = 'open' then creation_date end) asc,
         (case when status = 'closed' then creation_date end) desc

Upvotes: 4

Related Questions