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:

1. ORDER BY clause must be placed last in the SELECT statement.

2. Rows can be sorted by either ascending order or descending order. See Practice #1.

3. 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.

4. If sorting is used on a character type column, normally the sort is conducted in a case-insensitive fashion.

5. You can force a case-sensitive sort for a column by using the keyword BINARY after ORDER BY clause. See Practice #3 and #4.

6. You can sort on multiple columns, and you can sort different columns in different directions. See Practice #5.

7. The ORDER BY clause can use column alias for sorting. See Practice #6.

8. 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.

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

10. 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.

11. 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.

12. 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.

Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

Practice #1: Sort column in descending order.

-- 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:

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:

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:

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:

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:

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:

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):

Query 2 result- 77 rows returned (Use column alias):

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:

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:

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):

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

-- Generate random numbers
SELECT RAND();

Other tutorials in this category

1. The Basic SELECT Statement

2. Using Column Alias in SELECT Statement

3. Using Literal Character Strings

4. Perform Arithmetic Operations

5. Use WHERE Clause to Conditionally Select Rows

6. Using LIMIT Clause to Constrain the Number of Rows Retrieved

7. Using Comparison Operators, Part I

8. Using Comparison Operators, Part II

9. Using LIKE Comparison Operator

10. Using Logical Operators

11. Eliminating Duplicate Rows

Back to Tutorial Index Page