Mysql Sort records by some specific field values

Suppose the following scenario:

We have a mysql table that stores all orders of an online store. Each order has off course a field that represents the status of this order. That can be ‘pending’, ‘payment_received’, ‘approved’, ’shipped’, ‘completed’ .

Those statuses are found in the majority off good online stores so this is a practical theory.

Now what if we want to list all orders, but sorted like 1st the ‘pending’ ones, 2nd the ‘payment_received’ ones, 3rd the ‘approved’ one 4th the shipped ones and last the ‘completed’. Off curse if you think of it from the beginning you’ll probably structure your table such way so that is wasy to be done by a simple sort by . But what if your application is already done and you just need to add this small thing?

Well mysql can save from a ton of work . You can use advanced decision statements on the group by. We’ll just use a switch type of statement and we’ll basicly assign each value of the status a numeric value and we’ll sort on that.

Here is how the order by clause would look like :

ORDER BY

CASE order_status

      WHEN 'pending'   THEN 1

      WHEN 'payment_received'   THEN 2

      WHEN 'approved' THEN 3

      WHEN 'shipped'   THEN 4

      WHEN 'completed' THEN 5

      ELSE 99999

END

The above will do exactly what we want. Will test the values of order_status field and will assign to that a numerical value used jst for search. Such a nice thing that does your life much easier when you really need it…

Post a comment