Reputation: 2758
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
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