Order By clause
Prerequisites
For this lab, you must have the “classicmodels” database. If you do not have the classicmodels database setup, click [here](link to download the database script file).
Learning Objectives
This lab will demonstrate how to sort the rows in a result set using the ORDER BY clause. By the end of this lab, learners will be able to use the ORDER BY clause in SQL.
Introduction
When you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified. To sort the rows in the result set, add the ORDER BY clause to the SELECT statement.
Example 1: Sort a result set by an expression.
See the following orderdetails table from the sample database: orderdetails
orderNumber
productCode
quantityOrdered
priceEach
orderLineNumber
The following query selects the order line items from the orderdetails table. It calculates the subtotal for each line item, and sorts the result based on the subtotal.
Output
orderNumber | orderLineNumber | quantityOrdered * priceEach |
---|---|---|
10403 | 9 | 11503.14 |
10405 | 5 | 11170.52 |
10407 | 2 | 10723.60 |
10404 | 3 | 10460.16 |
10312 | 3 | 10286.40 |
To make the query more readable, you can assign an alias to a column in the SELECT statement and use that column alias in the ORDER BY clause:
In this example, we use subtotal as the column alias for the expression quantityOrdered * priceEach, and sort the result set by the subtotal alias.
Output
orderNumber | orderLineNumber | subtotal |
---|---|---|
10403 | 9 | 11503.14 |
10405 | 5 | 11170.52 |
10407 | 2 | 10723.60 |
10404 | 3 | 10460.16 |
10312 | 3 | 10286.40 |
Example 2: MySQL ORDER BY and NULL Values
In SQL, NULL comes before non-NULL values. Therefore, when you use the ORDER BY clause with the ASC option, NULLs appear first in the result set.
For example, the following query uses the ORDER BY clause to sort employees by values in the reportsTo column:
Output
firstName | lastName | reportsTo |
---|---|---|
Diane | Murphy | NULL |
Mary | Patterson | 1002 |
Jeff | Firrelli | 1002 |
William | Patterson | 1056 |
Gerard | Bondur | 1056 |
However, if you use the ORDER BY clause with the DESC option, NULLs will appear last in the result set. For example:
Output
firstName | lastName | reportsTo |
---|---|---|
Mami | Nishi | 1056 |
Mary | Patterson | 1002 |
Jeff | Firrelli | 1002 |
Diane | Murphy | NULL |
Summary
Use the ORDER BY clause to sort the result set by one or more columns.
Use the ASC option to sort the result set in ascending order, and the DESC option to sort the result set in descending order.
The ORDER BY clause is evaluated after the FROM and SELECT clauses.
In MySQL, NULL is lower than non-NULL values
Last updated