# 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). 

{% file src="<https://2619257483-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCAQ4m4veZXutSlrX8aix%2Fuploads%2FCmUeWhS2tHkuFMlBZ7Kg%2Fclassicmodels.sql?alt=media&token=b24972f3-6516-4d40-876a-eaa18facb554>" %}

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

```sql
SELECT    orderNumber, orderlinenumber, quantityOrdered * priceEach
FROM    orderdetails
ORDER BY   quantityOrdered * priceEach DESC;

```

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

```sql
SELECT
    orderNumber,
    orderLineNumber,
    quantityOrdered * priceEach AS subtotal
FROM    orderdetails
ORDER BY subtotal DESC;

```

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:

```sql
SELECT    firstName, lastName, reportsTo
FROM    employees
ORDER BY reportsTo;

```

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

```sql
SELECT    firstName, lastName, reportsTo
FROM    employees
ORDER BY reportsTo DESC;

```

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

{% file src="<https://2619257483-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FCAQ4m4veZXutSlrX8aix%2Fuploads%2FY7JeHlV7A4LV9J58taLa%2FGLAB%20-%20304.4.1%20-%20Order%20By%20clause.sql?alt=media&token=f0f4e6c1-2bb5-4be0-8d11-f0f48bc468fe>" %}