ORDER BY CLAUSE

This SQL tutorial explains how to use the SQL ORDER BY clause with syntax and examples. Click the "Try It" button next to an example to test the ORDER BY clause for yourself in our SQL Editor.

DESCRIPTION

The SQL ORDER BY clause is used to sort the records in the result set for a SELECT statement.

SYNTAX

The syntax for the SQL ORDER BY clause is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. Conditions that must be met for the records to be selected.

ASC

Optional. ASC sorts the result set in ascending order by expression. This is default behavior, if no modifier is provider.

DESC

Optional. DESC sorts the result set in descending order by expression.

NOTE

• If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order. This is equivalent to ORDER BY expression ASC.

DDL/DML FOR EXAMPLES

If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database!

Get DDL/DML

EXAMPLE - SORTING WITHOUT USING ASC/DESC ATTRIBUTE

The SQL ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the SQL ORDER BY clause, the sort order is defaulted to ASC or ascending order. Let's explore this further.

In this example, we have a table called customers with the following data:

customer_id	last_name	first_name	favorite_website
4000	Jackson	Joe	techonthenet.com
5000	Smith	Jane	digminecraft.com
6000	Ferguson	Samantha	bigactivities.com
7000	Reynolds	Allen	checkyourmath.com
8000	Anderson	Paige	NULL
9000	Johnson	Derek	techonthenet.com

Enter the following SQL statement:

Try It

SELECT *
FROM customers
ORDER BY last_name;

These are the results that you should see:

customer_id	last_name	first_name	favorite_website
8000	Anderson	Paige	NULL
6000	Ferguson	Samantha	bigactivities.com
4000	Jackson	Joe	techonthenet.com
9000	Johnson	Derek	techonthenet.com
7000	Reynolds	Allen	checkyourmath.com
5000	Smith	Jane	digminecraft.com

This example would return all records from the customers sorted by the last_name field in ascending order and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT *
FROM customers
ORDER BY last_name ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

EXAMPLE - SORTING IN DESCENDING ORDER

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause. Let's take a closer look.

In this example, we have a table called suppliers with the following data:

supplier_id	supplier_name	city
100	Microsoft	Redmond
200	RIM	Waterloo
300	Oracle	Redwood City
400	Google	Mountain View
500	Intel	Santa Clara
600	Samsung	Seoul

Enter the following SQL statement:

Try It

SELECT *
FROM suppliers
WHERE supplier_id > 200
ORDER BY supplier_id DESC;

These are the results that you should see:

supplier_id	supplier_name	city
600	Samsung	Seoul
500	Intel	Santa Clara
400	Google	Mountain View
300	Oracle	Redwood City

This example would sort the result set by the supplier_id field in descending order.

EXAMPLE - SORTING BY RELATIVE POSITION

You can also use the SQL ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1, the second field is 2, the third field is 3, and so on.

In this example, we have a table called products with the following data:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY 1 DESC;

These are the results that you should see:

product_id	product_name
7	Kleenex
6	Sliced Ham
4	Apple
3	Orange
2	Banana
1	Pear

This example would sort the results by the product_id field in descending order, since the product_id field is in position #1 in the result set and would be equivalent to the following SQL ORDER BY clause:

Try It

SELECT product_id, product_name
FROM products
WHERE product_name <> 'Bread'
ORDER BY product_id DESC;

EXAMPLE - USING BOTH ASC AND DESC ATTRIBUTES

When sorting your result set using the SQL ORDER BY clause, you can use the ASC and DESC attributes in a single SELECT statement.

In this example, let's use the same products table as the previous example:

product_id	product_name	category_id
1	Pear	50
2	Banana	50
3	Orange	50
4	Apple	50
5	Bread	75
6	Sliced Ham	25
7	Kleenex	NULL

Now enter the following SQL statement:

Try It

SELECT *
FROM products
WHERE product_id <> 7
ORDER BY category_id DESC, product_name ASC;

These are the results that you should see:

product_id	product_name	category_id
5	Bread	75
4	Apple	50
2	Banana	50
3	Orange	50
1	Pear	50
6	Sliced Ham	25

This example would return the records sorted by the category_id field in descending order, with a secondary sort byproduct_name in ascending orde