This tutorial demonstrates how to use the SQL} ORDER BY clause to sort the result set in ascending or descending order according to the specified criteria.
1. Introduction to SQL order by clause
When you use the SELECT statement to query the data in a table, the order of the rows displayed in the result set may not match your expectations.
In some cases, the rows displayed in the result set are arranged in the order in which they are physically stored in the table. However, if the query optimizer uses indexes to process queries, the row records are displayed as they are stored in index key order. Therefore, the order of rows in the result set is uncertain or unpredictable.
Query optimizer is a built-in software component in database system, which is used to determine the most effective way for SQL statement to query request data.
To accurately specify the row order in the result set, add an ORDER BY clause in the SELECT statement, as follows:
SELECT
column1, column2
FROM
table_name
ORDER BY column1 ASC ,
column2 DESC;
In this syntax, the ORDER BY clause is placed after the FROM clause. If the SELECT statement contains a WHERE clause, the ORDER BY clause must be placed after the WHERE clause.
For more tutorials, visit http://www.manongzj.com
To sort the result set, specify the columns to sort and the type of sort order:
- Ascending order (expressed by ASC)
- Descending order (represented by DESC)
If no sort order is specified, the database system usually sorts the result set in ascending order (ASC) by default.
When multiple columns are included in the ORDER BY clause, the database system first sorts the result set according to the first column, then sorts the sorted result set according to the second column, and so on.
2. Example of SQL order by clause
We will use Sample database The employees table in.
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 100 | Steven | Lee | 1987-06-17 | 24000 | | 101 | Neena | Wong | 1989-09-21 | 17000 | | 102 | Lex | Liang | 1993-01-13 | 17000 | | 103 | Alexander | Lee | 1990-01-03 | 9000 | | 104 | Bruce | Wong | 1991-05-21 | 6000 | | 105 | David | Liang | 1997-06-25 | 4800 | | 106 | Valli | Chen | 1998-02-05 | 4800 | | 107 | Diana | Chen | 1999-02-07 | 4200 | ... ... | 200 | Jennifer | Zhao | 1987-09-17 | 4400 | | 201 | Michael | Zhou | 1996-02-17 | 13000 | | 202 | Pat | Zhou | 1997-08-17 | 6000 | | 203 | Susan | Zhou | 1994-06-07 | 6500 | | 204 | Hermann | Wu | 1994-06-07 | 10000 | | 205 | Shelley | Wu | 1994-06-07 | 12000 | | 206 | William | Wu | 1994-06-07 | 8300 | +-------------+------------+-----------+------------+--------+ 40 rows in set
It appears that row records are displayed in the order stored in the employees table. To sort employees alphabetically by name, add an ORDER BY clause to query as follows:
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
first_name;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 103 | Alexander | Lee | 1990-01-03 | 9000 | | 115 | Alexander | Su | 1995-05-18 | 3100 | | 114 | Avg | Su | 1994-12-07 | 11000 | | 193 | Britney | Zhao | 1997-03-03 | 3900 | | 104 | Bruce | Wong | 1991-05-21 | 6000 | ... ... | 117 | Sigal | Zhang | 1997-07-24 | 3000 | | 100 | Steven | Lee | 1987-06-17 | 24000 | | 203 | Susan | Zhou | 1994-06-07 | 6500 | | 106 | Valli | Chen | 1998-02-05 | 4800 | | 206 | William | Wu | 1994-06-07 | 8300 | +-------------+------------+-----------+------------+--------+ 40 rows in set
Now, the result set is pressed first_ The values of the name column are sorted alphabetically.
3. SQL ORDER BY - example of sorting by multiple columns
To sort employees in ascending order by first_name column and then in descending order by last_name, use the following statement:
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
first_name,
last_name DESC;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 115 | Alexander | Su | 1995-05-18 | 3100 | | 103 | Alexander | Lee | 1990-01-03 | 9000 | | 114 | Avg | Su | 1994-12-07 | 11000 | | 193 | Britney | Zhao | 1997-03-03 | 3900 | | 104 | Bruce | Wong | 1991-05-21 | 6000 | | 179 | Charles | Yang | 2000-01-04 | 6200 | ... ... | 113 | Min | Su | 1999-12-07 | 6900 | | 122 | Min | Liu | 1995-05-01 | 7900 | | 108 | Nancy | Chen | 1994-08-17 | 12000 | | 101 | Neena | Wong | 1989-09-21 | 17000 | | 202 | Pat | Zhou | 1997-08-17 | 6000 | | 192 | Sarah | Zhang | 1996-02-04 | 4000 | | 123 | Shanta | Liu | 1997-10-10 | 6500 | | 205 | Shelley | Wu | 1994-06-07 | 12000 | | 116 | Shelli | Zhang | 1997-12-24 | 3000 | | 117 | Sigal | Zhang | 1997-07-24 | 3000 | | 100 | Steven | Lee | 1987-06-17 | 24000 | | 203 | Susan | Zhou | 1994-06-07 | 6500 | | 106 | Valli | Chen | 1998-02-05 | 4800 | | 206 | William | Wu | 1994-06-07 | 8300 | +-------------+------------+-----------+------------+--------+ 40 rows in set
First, the database system sorts the result set of the first column (first_name) in ascending order, and then sorts the sorting results sorted by last_name in descending order. Please note the location change of two employees: Alexander Su and Alexander Lee.
4. SQL ORDER BY - sort by numeric column example
SQL is used to sort the data alphabetically, as shown in the above example, and sort the data numerically. For example, the following statement selects employee data and sorts the salary column results in descending order:
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
salary DESC;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 100 | Steven | Lee | 1987-06-17 | 24000 | | 101 | Neena | Wong | 1989-09-21 | 17000 | | 102 | Lex | Liang | 1993-01-13 | 17000 | | 145 | John | Liu | 1996-10-01 | 14000 | | 146 | Karen | Liu | 1997-01-05 | 13500 | | 201 | Michael | Zhou | 1996-02-17 | 13000 | | 108 | Nancy | Chen | 1994-08-17 | 12000 | | 205 | Shelley | Wu | 1994-06-07 | 12000 | ... ... | 193 | Britney | Zhao | 1997-03-03 | 3900 | | 115 | Alexander | Su | 1995-05-18 | 3100 | | 116 | Shelli | Zhang | 1997-12-24 | 2900 | | 117 | Sigal | Zhang | 1997-07-24 | 2800 | | 126 | Irene | Liu | 1998-09-28 | 2700 | | 118 | Guy | Zhang | 1998-11-15 | 2600 | | 119 | Karen | Zhang | 1999-08-10 | 2500 | +-------------+------------+-----------+------------+--------+ 40 rows in set
5. SQL ORDER BY - sort by date example
In addition to characters and numbers, SQL can sort the result set by date. The following statements press hire_ The values in the date column sort employees in ascending order.
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
hire_date;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 100 | Steven | Lee | 1987-06-17 | 24000 | | 200 | Jennifer | Zhao | 1987-09-17 | 4400 | | 101 | Neena | Wong | 1989-09-21 | 17000 | | 103 | Alexander | Lee | 1990-01-03 | 9000 | | 104 | Bruce | Wong | 1991-05-21 | 6000 | | 102 | Lex | Liang | 1993-01-13 | 17000 | | 204 | Hermann | Wu | 1994-06-07 | 10000 | ...... | 107 | Diana | Chen | 1999-02-07 | 4200 | | 178 | Kimberely | Yang | 1999-05-24 | 7000 | | 119 | Karen | Zhang | 1999-08-10 | 2500 | | 113 | Min | Su | 1999-12-07 | 6900 | | 179 | Charles | Yang | 2000-01-04 | 6200 | +-------------+------------+-----------+------------+--------+ 40 rows in set
To view the latest employees who have just joined the company, you can sort the employees in descending order of hire_date, as shown in the following statement:
SELECT
employee_id,
first_name,
last_name,
hire_date,
salary
FROM
employees
ORDER BY
hire_date DESC;
Execute the above query statement and get the following results-
+-------------+------------+-----------+------------+--------+ | employee_id | first_name | last_name | hire_date | salary | +-------------+------------+-----------+------------+--------+ | 179 | Charles | Yang | 2000-01-04 | 6200 | | 113 | Min | Su | 1999-12-07 | 6900 | | 119 | Karen | Zhang | 1999-08-10 | 2500 | | 178 | Kimberely | Yang | 1999-05-24 | 7000 | ...... | 104 | Bruce | Wong | 1991-05-21 | 6000 | | 103 | Alexander | Lee | 1990-01-03 | 9000 | | 101 | Neena | Wong | 1989-09-21 | 17000 | | 200 | Jennifer | Zhao | 1987-09-17 | 4400 | | 100 | Steven | Lee | 1987-06-17 | 24000 | +-------------+------------+-----------+------------+--------+ 40 rows in set
In this tutorial, you learned how to use the SQL ORDER BY clause to sort the result set according to the ascending or descending order of one or more columns.