mysqlsql-order-by

Can I order by a conditional combination of fields in MySQL?


Example Fiddle

    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

Solution

  • SELECT * FROM Demo 
    ORDER BY 
      CASE 
        WHEN Status='Cancelled' THEN 2
        WHEN Status='Invoiced' AND InvoiceStatus='Paid' THEN 1
        ELSE 0
      END,
      JobId;