Using ORDER BY and FIELD function to get single value to each custom list

I have a table named orders having 2 columns orderNumber & status as follow :

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:

orderNumber | status
----------------------------
10108       |   In Process  |
10104       |   Cancelled   |
10101       |   Resolved    |
10107       |   Disputed    |
10100       |   Shipped     |

I have try this query for that:

SELECT orderNumber, status 
FROM orders 
ORDER BY FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped')

But this give me all sorted result.

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:

SELECT MIN(orderNumber) AS orderNumber, status
FROM orders
GROUP BY status
ORDER BY FIELD(status, 'In Process', 'On Hold', 'Cancelled', 'Resolved', 'Disputed', 'Shipped')

Output:

orderNumber     status
10108           In Process
10104           Cancelled
10101           Resolved
10107           Disputed
10100           Shipped

Demo on db-fiddle