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 appendASC
at the end of eachORDER BY
column. - You can mix
ASC
(ascending) andDESC
(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).ENUM
values 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'
forENUM('b', 'a')
. The empty string sorts before nonempty strings, andNULL
values sort before all other enumeration values.
This post was published (and was last revised ) by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.