CODEWITHSUNDEEP
mysqlsqlsorting
h345k34cr
h345k34cr

Reputation: 3460

SQL special sort order under certain circumstances

I have the following scenario:

The whole table should be sorted by a fictional priority, which means:

So, after I explained the scenario, I have the following question:

Is it possible to add conditions to ORDER BY to make something like that possible, or is there any other way to implement that?

Edit: The ticket status is an integer (open = 0, customerResponded = 10, waitingForCustomer = 20, closed = 100).

Upvotes: 1

Views: 54

Answers (2)

GarethD
GarethD

Reputation: 69749

You can just use a case expression inside your order by:

ORDER BY status,
        CASE WHEN status IN (0, 10) THEN Datetime END ASC,
        CASE WHEN status IN (20, 100) THEN Datetime END DESC

Upvotes: 3

h345k34cr
h345k34cr

Reputation: 3460

Thank's to GarethD, I found a solution, which is but a bit different from his:

ORDER BY
    CASE WHEN t.`ticket_status` IN (0, 10) THEN 0 ELSE t.`ticket_status` END ASC,
    CASE WHEN t.`ticket_status` IN (0, 10) THEN m.`message_created`
                                           ELSE -m.`message_created` END ASC

First of all, I check whether I have the status open=0 or customerResponded=10.

If so, I order by 0 (which means just equally on top of the list).

Else, I order by the ticket status (waitingForCustomer has higher priority than closed).

The subordinate order instruction now orders by message_created ASC or by -message_created ASC (which is technically message_created DESC) to order by date correctly in the subordinate "group".

Upvotes: 0

Related Questions