SQL: ORDER BY Clause
This SQL tutorial explains how to use the SQL ORDER BY clause with syntax and examples.
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 ORDER BY clause in SQL 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 the 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!
Example - Sorting Results in Ascending Order
To sort your results in ascending order, you can specify the ASC attribute. If no value (ASC or DESC) is provided after a field in the ORDER BY clause, the sort order will default to 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 | google.com |
5000 | Smith | Jane | digminecraft.com |
6000 | Ferguson | Samantha | bigactivities.com |
7000 | Reynolds | Allen | checkyourmath.com |
8000 | Anderson | Paige | NULL |
9000 | Johnson | Derek | google.com |
Enter the following SQL statement:
SELECT * FROM customers ORDER BY last_name;
There will be 6 records selected. 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:
SELECT * FROM customers ORDER BY last_name ASC;
Most programmers omit the ASC attribute if sorting in ascending order.
Example - Sorting Results 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 | state |
---|---|---|---|
100 | Microsoft | Redmond | Washington |
200 | Mountain View | California | |
300 | Oracle | Redwood City | California |
400 | Kimberly-Clark | Irving | Texas |
500 | Tyson Foods | Springdale | Arkansas |
600 | SC Johnson | Racine | Wisconsin |
700 | Dole Food Company | Westlake Village | California |
800 | Flowers Foods | Thomasville | Georgia |
900 | Electronic Arts | Redwood City | California |
Enter the following SQL statement:
SELECT * FROM suppliers WHERE supplier_id > 400 ORDER BY supplier_id DESC;
There will be 5 records selected. These are the results that you should see:
supplier_id | supplier_name | city | state |
---|---|---|---|
900 | Electronic Arts | Redwood City | California |
800 | Flowers Foods | Thomasville | Georgia |
700 | Dole Food Company | Westlake Village | California |
600 | SC Johnson | Racine | Wisconsin |
500 | Tyson Foods | Springdale | Arkansas |
This example would sort the result set by the supplier_id field in descending order.
Example - Sorting Results 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:
SELECT product_id, product_name FROM products WHERE product_name <> 'Bread' ORDER BY 1 DESC;
There will be 6 records selected. 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:
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:
SELECT * FROM products WHERE product_id <> 7 ORDER BY category_id DESC, product_name ASC;
There will be 6 records selected. 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 by product_name in ascending order.
No comments:
Post a Comment