CREATE TABLE Demo (JobID INT(11), Status VARCHAR(250), InvoiceStatus VARCHAR(250));
INSERT INTO Demo (JobID, Status, InvoiceStatus) VALUES (1, "Active", NULL), (2, "Cancelled", NULL), (3, "Invoiced", "Not Paid"), (4, "Invoiced", "Paid"), (5, "No Invoice Required", "Paid"), (6, "Active", NULL);
SELECT * FROM Demo ORDER BY FIELD (Status, "Cancelled") ASC, JobID
The desired output is to have the rows ordered by JobID
, but have any jobs where the Status
is "Cancelled" at the end, and any jobs where the Status
is "Invoiced" and the InvoiceStatus
is "Paid" also at the end, but before Cancelled jobs.
I found out how to get "Cancelled" at the end with ORDER BY Field()
, but I'm not sure how to apply a similar thing only when two fields match the given values ("Invoiced" and "Paid").
So with the above examples, the desired output would be
JobID Status InvoiceStatus
-----------------------------------------
1 Active NULL
3 Invoiced Not Paid
5 No Invoice Required Paid
6 Active NULL
4 Invoiced Paid
2 Cancelled NULL
SELECT * FROM Demo
ORDER BY
CASE
WHEN Status='Cancelled' THEN 2
WHEN Status='Invoiced' AND InvoiceStatus='Paid' THEN 1
ELSE 0
END,
JobId;