How To Custom Sort In SQL ORDER BY Clause?

This article showcases various methods of doing custom sorting in SQL. For all examples in this article, we'll be creating a custom sort order on the following table of currencies, ranked by their order of importance:

id	currency_code	currency_name	currency_symbol
1	AUD	Australian Dollars	$
2	CAD	Canadian Dollars	$
3	EUR	Euros	€
4	GBP	Great Britian Pound	£
5	JPY	Japanese Yen	¥
6	USD	US Dollars	$

---

Using Control Flow Functions

CASE Operator:

Syntax:

CASE value 
WHEN [compare_value] THEN result 

[WHEN [compare_value] THEN result ...] 

[ELSE result] 
END

CASE 
WHEN [condition] THEN result 

[WHEN [condition] THEN result ...] 

[ELSE result] 
END

The first version returns the result where value=compare_value. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.

Example:

Using the CASE operator, we can specify a custom order rank for each item of concern like so:

ORDER BY (
    CASE currency_code
    
    WHEN 'USD'
    THEN 1
    
    WHEN 'EUR'
    THEN 2
    
    WHEN 'JPY'
    THEN 3
    
    WHEN 'GBP'
    THEN 4
    
    WHEN 'CAD'
    THEN 5
    
    WHEN 'AUD'
    THEN 6
    
    END
) ASC

IF/ELSE Construct:

Syntax:

IF(expr1, expr2, expr3)

If expr1 is TRUE (i.e. expr1 != 0 and expr1 != NULL) then IF()returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.

Example:

If we just wanted US Dollars to appear before everything else, we could simply use IF/ELSE construct like so:

ORDER BY IF(currency_code = 'USD', 1, 2) ASC, currency_name ASC

The currency_name after IF/ELSE makes sure that all other fields than 'USD' are sorted by currency_name.

---

MySQL Specific

Using FIELD() Function:

Syntax:

FIELD(str, str1, str2, str3, ...)

Returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found or is NULL.

Example:

Like we did in the CASE operator, we can specify a custom order rank for each item of concern like so:

ORDER BY FIELD(currency_code, 'USD', 'EUR', 'JPY', 'GBP', 'CAD', 'AUD') ASC

It makes your SQL less portable, as other Databases might not have such a function.

---

Using A Sort Order Column

We could alter our table by adding a sort ranking column (for example, 'currency_rank') like so:

id	currency_code	currency_name	currency_symbol	currency_rank
1	AUD	Australian Dollars	A$	6
2	CAD	Canadian Dollars	C$	5
3	EUR	Euros	€	2
4	GBP	Great Britian Pound	£	4
5	JPY	Japanese Yen	¥	3
6	USD	US Dollars	$	1

Using the above table syntax, we could simply ORDER BY currency_rank. This could be useful where the list to sort is too large.

---

Closing Remarks

• When the list of currencies (or other values to sort by) get much larger, it's better to have a separate column or table for sort-order.
• For columns that could potentially have duplicates, specify a secondary (or tertiary etc.) ORDER BY column name. For example: ORDER BY currency_symbol ASC, currency_name ASC
• By default all columns specified in ORDER BY clause are sorted in ascending order. Therefore, there's no need to explicitly append ASC at the end of each ORDER BY column.
• You can mix ASC (ascending) and DESC (descending) order like so: ORDER BY currency_symbol ASC, currency_name DESC
• You could use the ENUM column type (if it's available in your DBMS). ENUMvalues are sorted based on their index numbers, which depend on the order in which the enumeration members were listed in the column specification. For example, 'b' sorts before 'a' for ENUM('b', 'a'). The empty string sorts before nonempty strings, and NULL values sort before all other enumeration values.