- The Select Statement
- The Select Clause
- The Where Clause
- The Order By Clause
- Summary
This chapter is from the book
This chapter is from the book
This chapter is from the book
The Order By Clause
The order by clause determines how the rows of the result table are sorted when they are printed or displayed on the screen. If you leave out the order by clause, you are saying that you do not care about this order and you are giving the computer permission to display the rows of the result in any order.
2-16 Overview of the order by clause
In working with most of the tables in this book, you can get acceptable results even if you do not write an order by clause because most of the tables are small. They contain only a few rows. However, when you work with larger tables, it is essential to use an order by clause.
This section shows the syntax of the order by clause and a few examples of it. The clause contains a list of columns and a specification for each of these columns to sort them in either ascending order or descending order.
The first column listed in the order by clause is the primary sort order. The columns that are listed after the first one are used only when two rows have identical values in the first column. This rule applies to all the columns. For example, the third column is only used to sort the rows that have identical values in the first two columns of the order by clause.
Ascending order is the default. It is usually not specified. To sort on a column in descending order, desc must always be specified.
Columns are usually specified by their names. Another method is to specify a number — this is the position of the column within the select clause. This is an older method that is being phased out. Some brands of SQL allow you to use a column alias in an order by clause. Oracle allows this, but Access does not.
A column can sometimes be listed in the order by clause without listing it in the select clause. However, it is good programming practice to list in the select clause all the columns used in the order by clause.
In Oracle, nulls are sorted at the bottom. In Access, they are sorted at the top. Other slight differences in the sort order can occur depending on a variety of factors, such as
Which SQL product you are using
Whether you are using a small computer or a large computer
Whether you are using a special alphabet
Options set by your database administrator (DBA)
Syntax of the order by clause
|
order by a list of column names |
|
|
order by a list of numbers |
|
Sort order options for each column
|
asc |
|
|
desc |
|
Examples
order by employee_id
order by last_name, first_name
order by hire_date desc,
last_name,
first_name
2-17 Sorting the rows by several columns in ascending order
This section shows a query with two columns in its order by clause, both of which are sorted in ascending order.
Task
List the department codes and last names of all the employees, except for employee 209. Sort the rows of the result table on both columns in ascending order.
Oracle & Access SQL
select dept_code,
last_name
from l_employees
where not (employee_id = 209)
order by dept_code, 
last_name; 
Beginning table (l_employees table)
Result table
Notes
|
|
The rows of the result table are sorted first and primarily on the dept_code column. For instance, all four rows with a dept_code of SAL are sorted before the three rows with SHP. |
|
|
The rows with identical values in the dept_code column are then sorted on the last_name column. Within the SAL department code, the last names are put in ascending alphabetic order. Within the SHP department code, the names are put in a separate ascending alphabetic order. |
|
Note the order of these rows in the result table. Here, for the employees within any particular department, the last names are in ascending order. In the next section, we change the order and place the last names in descending order. |
2-18 Sorting the rows by several columns in various orders
This shows the same query as in the previous section, except that the sort on the last_name column is in descending order. The contrast with the result table in the previous section shows the difference.
Task
List the department codes and last names of all the employees, except for employee 209. Sort the rows of the result table in ascending order on the dept_code column and in descending order on the last_name column.
Oracle & Access SQL
select dept_code,
last_name
from l_employees
where not (employee_id = 209)
order by dept_code,
last_name ;
Beginning table (l_employees table)
Result table
Notes
|
|
The rows of the result table are sorted first and primarily on the dept_code column. |
|
|
All the rows with the same value in the dept_code column are sorted on the last_name column in descending order. This is applied twice, once with the SAL department codes and again with the SHP ones. |
|
|
Note the order of these rows. Compare the order here with the order shown in the previous result table. |
2-19 The whole process so far
Here is a quick summary of the process a select statement describes. Note that clauses of the select statement are processed in a different order than they are written.
