How to Custom Sort in SQL ORDER BY Clause?

Custom Sort in SQL ORDER BY Clause

This article show you how to use custom sorting using SQL ORDER BY CLAUSE in SQL Query. In SQL Query mostly there will be a need to sort data either in ASC order or DESC order. Sometime we have a requirement to sort result in Custom Order. We’ll be creating a custom sort order on the following table , ranked by their order of importance:

4GBPGreat Britian Pound£
6USDUS Dollars$

Using CASE Operator:

SELECT * FROM currency
ORDER BY case when currencycode = 'CAD' then 1
              when currencycode = 'AUD' then 2
              when currencycode = 'EUR' then 3
              when currencycode = 'USD' then 4
              else 5
         end asc

IF / ELSE Construct:

SELECT * FROM currency ORDER BY IF(currencycode = 'USD', 1, 2) ASC

Using FIELD() Function:

SELECT * FROM currency ORDER BY FIELD(currencycode, 'USD', 'EUR', 'JPY', 'GBP', 'CAD', 'AUD') ASC

2 Comments on “How to Custom Sort in SQL ORDER BY Clause?”

  1. An outstanding share! I have just forwarded this onto a colleague
    who has been conducting a little homework on this. And he actually ordered
    me breakfast simply because I discovered it for him…
    lol. So let me reword this…. Thank YOU for the meal!!
    But yeah, thanks for spending some time to discuss this matter here on your web site.

Leave a Reply

Your email address will not be published. Required fields are marked *