Sorting in MySQL

Examples of different sorting variants in MySQL.

Contents:

On multiple columns

List the columns to sort by separated by commas and specify its direction:

SELECT * FROM articles ORDER BY a_title ASC, a_date DESC

Sorting by a list of values

SELECT * FROM articles ORDER BY FIELD( a_season, 'весна','лето','осень','зима' )

The FIELD() function searches for the value specified in the first parameter among the values listed in the remaining parameters, and returns its ordinal position. When executing the query, the first parameter of the FIELD function will receive values from the field a_season, and thus the records will be sorted in the specified order.

Type conversion to number

SELECT * FROM metadata ORDER BY ( m_value + 0 )

m_value will be interpreted as a number.

Type conversion to string

SELECT left( m_value, 20 ) AS m_value_str FROM articles ORDER BY m_value_str

m_value_str will be interpreted as a string (even if it was a number). The left function returns a string containing the first N characters of the string. In this case, the first 20 characters will be selected (enough to convert an integer to a string).

Sorting text with case sensitivity

SELECT * FROM articles ORDER BY BINARY a_title

Sorting by a fragment of a string

SELECT SUBSTRING_INDEX( a_title, ' ', -1 ) AS a_title_part FROM wp_5_posts ORDER BY a_title_part

SUBSTRING_INDEX selects a substring from a string.

Sorting strings by their length

SELECT * FROM articles ORDER BY CHAR_LENGTH( a_title )

Sorting by several fields, one of which may be NULL

SELECT * FROM mytable ORDER BY IFNULL( due_date, '9999-12-31 23:59:59' ) ASC, priority DESC

The IFNULL() function returns the specified value if the expression is NULL; otherwise it returns the expression.

Sorting with a condition

SELECT * FROM mytable ORDER BY
CASE
	WHEN due_date = '0000-00-00' THEN '9999-12-31 23:59:59'
	ELSE due_date
END ASC,
priority DESC

The CASE condition allows treating the value of the due_date column as equal to '9999-12-31 23:59:59' if the actual value is '0000-00-00'.
The MySQL CASE syntax looks like this:

CASE
	WHEN condition1 THEN result1
	WHEN condition2 THEN result2
	WHEN conditionN THEN resultN
	ELSE result
END;