How to Sort Data in SQL SELECT Statement for MySQL

Sort Data by ORDER BY Clause

It's often easier to examine the result rows from a query when the rows are sorted in some meaningful way. To sort a query output from a particular SELECT statement, use ORDER BY clause.

Read Prerequisites for this tutorial and practices if you haven't done so.

Consider the following facts when using ORDER BY clause in a SELECT statement:

ORDER BY clause must be placed last in the SELECT statement.
Rows can be sorted by either ascending order or descending order. See Practice #1.
By default, if ascending or descending is not specified in ORDER BY clause, the result rows are always sorted by ascending order. See Practice #2.
If sorting is used on a character type column, normally the sort is conducted in a case-insensitive fashion.
You can force a case-sensitive sort for a column by using the keyword BINARY after ORDER BY clause. See Practice #3 and #4.
You can sort on multiple columns, and you can sort different columns in different directions. See Practice #5.
The ORDER BY clause can use column alias for sorting. See Practice #6.
A column that uses arithmetic operation can be used in the ORDER BY clause. The arithmetic expressions are evaluated first and then the evaluated result is used for the sorting. See Practice #7.
The column(s) used in ORDER BY clause does not have to be included in the result set. See Practice #8.
In an ORDER BY clause, you can use 1 to represent the first column in the SELECT clause, 2 for the second column in the SELECT clause, and so on. See Practice #9.
The result set from a SELECT statement can be randomly sorted by using MySQL built-in function RAND() in the ORDER BY clause. See Practice #10.
If an ORDER BY clause is not used in a SELECT statement, MySQL outputs the result in different ways based on what table type it is. For example, innoDB orders the result by how the data is stored in the table such as using linked lists, or sort the data by using an index if one exists.

Practice #1: Sort column in descending order.

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- In this query, the result is sorted in descending order.
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY ProductName DESC;

Query result set - 77 rows returned:
Sort in descending order by ProductName in SELECT statement

Practice #2: Sort column in ascending order by default.

/*
If ascending or descending is not specified 
in ORDER BY clause, the result rows are always 
sorted by ascending order.
 
In this query, the result is sorted in ascending 
order by default.
*/
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY ProductName;

Query result set - 77 rows returned:
Sort Products by ProductName in SELECT statement

Practice #3: Case-sensitive descending sort

Note that, before executing this query, in products table, you need to change the first letter to lower-case in two product names:

Aniseed Syrup ==> aniseed Syrup (ProductID 3)

Mishi Kobe Niku ==> mishi Kobe Niku (ProductID 9)

/*
Case-sensitive sort in descending order. 
 
In this query, ProductName is sorted in 
case-sensitive descending order.
*/
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY BINARY ProductName DESC;

Normally sorting on a character type column is conducted in a case-insensitive fashion. You can force a case-sensitive sort by using the keyword BINARY after ORDER BY clause.

Because lower-case letters are considered to have lower priority than upper-case letters, the result from this query displays the lower-case product names first in descending order.

Query result set - 77 rows returned:
Descending case-sensitive sort by ORDER BY clause

Practice #4: Case-sensitive ascending sort

/*
Case-sensitive sort in ascending order. 
 
In this query, ProductName is sorted in 
case-sensitive ascending order.
*/
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY BINARY ProductName ASC;

Because lower-case letters are considered to have lower priority than upper-case letters, the result from this query displays the upper-case product names first in ascending order.

Query result set - 77 rows returned:
Ascending case-sensitive sort by ORDER BY clause

Practice #5: Sorting on two columns

/*
In this query, sorting is conducted on two 
columns - ProductName and UnitsInStock.
*/
SELECT ProductID, ProductName, UnitsInStock
FROM products
ORDER BY UnitsInStock ASC, ProductName DESC;

Query result set - 77 rows returned:
Sorting on two columns

Practice #6: Sorting by column alias

/*
In this query, column alias StockQuantity is
used in ORDER BY clause for sorting.
*/
SELECT ProductID, ProductName, UnitsInStock AS StockQuantity
FROM products
ORDER BY StockQuantity ASC;

Query result set - 77 rows returned:
Sorting by column alias

Practice #7: Sorting by a column of arithmetic operation

/*
Query 1: In this query, UnitsInStock is multiplied 
by 3 and the arithmetic result is used in ORDER BY 
clause for sorting. Note that the ordering still works 
if UnitsInStock*3 is not part of the SELECT clause.
*/
SELECT ProductID, ProductName, UnitsInStock*3
FROM products
ORDER BY UnitsInStock*3 ASC;
 
/*
Query 2: In this query, arithmetic operation column 
is given an alias and the alias is used for sorting.
*/
SELECT ProductID, ProductName, UnitsInStock*3 AS StockQuantity
FROM products
ORDER BY StockQuantity ASC;

Query 1 result - 77 rows returned (Column alias is not used):
Sorting by arithmetic operation column

Query 2 result- 77 rows returned (Use column alias):
Sorting by arithmetic operation column

Practice #8: Sort by a column which is not included in the result set

/*
In this query, products are sorted by UnitsInStock 
column but it's not included in the result set.
*/
SELECT ProductID, ProductName
FROM products
ORDER BY UnitsInStock ASC;

The column(s) used in ORDER BY clause does not have to be included in the result set.

Query result set - 77 rows returned:
Column in ORDER BY clause is not included in result set

Practice #9: Use column position number for sorting

/*
Use column position number from SELECT clause to
represent the actual column name in ORDER BY clause.
 
In this query, 1 is used to represent the first column in 
the SELECT clause. 2 is used to represent the second column 
in the SELECT clause.
*/
SELECT ProductID, ProductName
FROM products
ORDER BY 2 DESC, 1;

Query result set - 77 rows returned:
Use column position number in ORDER BY clause.

Practice #10: Randomly sort a query result

/*
Randomly select data from employees table. Every time 
you execute the query, it produces a different result set.
 
The result is unpredictable as RAND() returns a random
floating-point value between 0 and 1.0. 
 
The random value always has 14 digits after the decimal 
point (e.g. 0.14726665350354)
*/
SELECT EmployeeID, LastName, FirstName, Photo 
FROM employees
ORDER BY RAND();

Query result set - 9 rows returned (yours can be different as it's random sort):
Randomly sort a query result.

To see how the random function works, execute the following query multiple times to see how the result changes:

-- Generate random numbers
SELECT RAND();

Next tutorial: Use LIMIT Clause to Constrain the Number of Rows Returned
Back to Section Index: Single Table SELECT Statement
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search