SQL ORDER BY Clause

In general, the rows in a result set returned from a query are not in any particular order. If you want your result set to be sorted, you will need to instruct the server to sort the results using the order by clause:

The order by clause is the mechanism for sorting your result set using either raw column data or expressions based on column data.

For example, here’s look at a query that returns all customers who rented a film on June 14, 2005: All the examples in this post uses the mysql sakila database .

SELECT c.first_name, 
	c.last_name,
	time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14';

If you would like the results to be in alphabetical order by last name, you can add the last_name column to the order by clause:

SELECT c.first_name, 
		c.last_name,
		time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name;

While it is not the case in this example, large customer lists will often contain multiple people having the same last name, so you may want to extend the sort criteria to include the person’s first name as well.

SELECT c.first_name, 
	c.last_name,
	time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY c.last_name, c.first_name;

Ascending versus Descending Sort Order –

When sorting, you have the option of specifying ascending or descending order via the asc and desc keywords. The default is ascending, so you will need to add the desc keyword if you want to use a descending sort. For example, the following query shows all customers who rented films on June 14, 2005, in descending order of rental time:

SELECT c.first_name, 
	c.last_name,
	time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY time(r.rental_date) desc;

Descending sorts are commonly used for ranking queries, such as “show me the top five account balances.” MySQL includes a limit clause that allows you to sort your data and then discard all but the first X rows.

Sorting via Numeric Placeholders-

If you are sorting using the columns in your select clause, you can opt to reference the columns by their position in the select clause rather than by name. This can be especially helpful if you are sorting on an expression, such as in the previous example. Here’s the previous example one last time, with an order by clause specifying a descending sort using the third element in the select clause:

SELECT c.first_name, 
	c.last_name,
	time(r.rental_date) rental_time
FROM customer AS c 
INNER JOIN rental AS r 
ON c.customer_id = r.customer_id
WHERE date(r.rental_date) = '2005-06-14'
ORDER BY 3 desc;

You might want to use this feature sparingly, since adding a column to the select clause without changing the numbers in the order by clause can lead to unexpected results.