I have a table named orders
having 2 columns orderNumber
& status
as follow :
JavaScript
x
orderNumber | status
----------------------------
10100 | Shipped |
10101 | Resolved |
10102 | Shipped |
10103 | Resolved |
10104 | Cancelled |
10105 | Shipped |
10106 | Cancelled |
10107 | Disputed |
10108 | In Process |
10109 | Cancelled |
I want to get single status with order number order by custom list like this:
JavaScript
orderNumber | status
----------------------------
10108 | In Process |
10104 | Cancelled |
10101 | Resolved |
10107 | Disputed |
10100 | Shipped |
I have try this query for that:
JavaScript
SELECT orderNumber, status
FROM orders
ORDER BY FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped')
But this give me all sorted result.
Advertisement
Answer
It looks like you want to group by status
, selecting the minimum orderNumber
for each status
value, and then sort according to your FIELD
expression. You can do that with this query:
JavaScript
SELECT MIN(orderNumber) AS orderNumber, status
FROM orders
GROUP BY status
ORDER BY FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped')
Output:
JavaScript
orderNumber status
10108 In Process
10104 Cancelled
10101 Resolved
10107 Disputed
10100 Shipped