conditional ORDER BY?

[Leeoniya]

Hello, currently i have custom sorting set up like this:
ORDER BY
FIELD(order_status_id, 1,2,7,3),
FIELD(ship_svc_id, 9,1,2,7,3) ASC,
date ASC
essentially this can be summed up as: "Newly Recieved" orders first, then most urgent shipping first, then oldest first.

however, for all orders with an order_status_id = 3 (shipped), i'd like to simply sort them by date DESC. can this be accomplished?

thanks,
Leon

[Leeoniya]

i think this works, but it's kind of lengthy:

(
SELECT
order_id,
ship_svc_id,
status_id,
date
FROM
orders
WHERE
status_id != 3
ORDER BY
FIELD(status_id, 1,2,7),
FIELD(ship_svc_id, 9,1,2,7,3) ASC,
date ASC
)

UNION

(
SELECT
order_id,
ship_svc_id,
status_id,
date
FROM
orders
WHERE
status_id = 3
ORDER BY
date DESC
)

anything shorter possible?

[Fumigator]

You can use CASE in the ORDER BY... maybe this would work? With a bit of tweaking perhaps.


ORDER BY
CASE
WHEN status_id = 3 THEN date DESC
ELSE
FIELD(status_id, 1,2,7),
FIELD(ship_svc_id, 9,1,2,7,3) ASC,
date ASC
END CASE

[Leeoniya]

it gives me a syntax error on this:
SELECT
id,
date,
status_id,
ship_svc_id
FROM
orders
ORDER BY
CASE
WHEN status_id = 3 THEN date DESC
ELSE FIELD(status_id, 1,2,7), FIELD(ship_svc_id, 9,1,2,7,3) ASC, date ASC
END CASE

Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC
ELSE FIELD(status_id, 1,2,7), FIELD(ship_svc_id, 9,1,2,7,3) ASC, date ' at line 10

Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000

[Fumigator]

Yeah I told you you'd have to tweak it :p

You may have to do two CASE statements:

ORDER BY
CASE WHEN status_id = 3 THEN date END DESC
,CASE WHEN status_id != 3 THEN ....


Play with it a bit, read the manual... it's tricky syntax.

[Leeoniya]

i think this did it:

FIELD(status_id, 3,1,2,7) DESC,
CASE WHEN status_id != 3 THEN FIELD(ship_svc_id, 9,1,2,7,3) END,
CASE WHEN status_id != 3 THEN date END,
CASE WHEN status_id = 3 THEN date END DESC

thx.