Sorting Rows
Objectives
This tutorial covers the following objectives:
- Construct a query to sort a result set in ascending or descending order
- State the order in which expressions are evaluated and calculated based on the rules of precedence
- Construct a query to order a result set using a column alias
- Construct a query to order a result set for single or multiple columns
Purpose
- By nature, most of us need order in our lives.
- Imagine if each time you had dinner, you had to look in every kitchen drawer or cabinet to find a knife and a fork?
- Ordering, grouping, and sorting makes finding things easier.
- Biologists group animals in phyla, astronomers order brightness of stars by magnitude, and Java programmers organize code in classes.
- Our everyday lives are ordered in many situations:
- Library books in library
- Grocery-store shelves
- Documents stored in file cabinets
- Being able to sort results is a convenient feature in SQL and enables programmers to display information in many different ways.
- For database design, business functions are ordered by entities and attributes; in database information, SQL uses the ORDER BY clause.
ORDER BY Clause
- Information sorted in ascending order is familiar to most of us.
- It’s what makes looking up a number in a phone book, finding a word in the dictionary, or locating a house by its street address relatively easy.
- SQL uses the ORDER BY clause to order data.
- The ORDER BY clause can specify several ways in which to order rows returned in a query.
- The default sort order is ascending.
- Numeric values are displayed lowest to highest.
- Date values are displayed with the earliest value first.
- Character values are displayed in alphabetical order.
- Null values are displayed last in ascending order and first in descending order.
- NULLS FIRST Specifies that NULL values should be returned before non-NULL values.
- NULLS LAST Specifies that NULL values should be returned after non-NULL values.
- The following employees example uses the ORDER BY clause to order hire_date in ascending (default) order.
SELECT last_name, hire_date
FROM employees
ORDER BY hire_date;

Sorting in Descending Order
- You can reverse the default order in the ORDER BY clause to descending order by specifying the DESC keyword after the column name in the ORDER BY clause.
SELECT last_name, hire_date
FROM employees
ORDER BY hire_date DESC;

If you use ASC or DESC in your ORDER BY clause it will influence the placement of NULL values: null values are displayed last in ascending order and first in descending order.
Furthermore, you can use NULLS FIRST to specify that NULL values should be returned before non-NULL values. NULLS LAST specifies that NULL values should be returned after non-NULL values.
Using Column Aliases
- You can order data by using a column alias.
- The alias used in the SELECT statement is referenced in the ORDER BY clause.
SELECT last_name, hire_date AS "Date
Started"
FROM employees
ORDER BY "Date Started";

Sorting with Other Columns
- It is also possible to use the ORDER BY clause to order output by a column that is not listed in the SELECT clause.
- In the following example, the data is sorted by the last_name column even though this column is not listed in the SELECT statement.
SELECT employee_id, first_name
FROM employees
WHERE employee_id < 105
ORDER BY last_name;

It is difficult to verify your results when you sort by a column that you’re not SELECTing. In the real world, you would run your query selecting the last_name column until you were sure you were getting the right data. Then you could remove that column from your SELECT statement.
Order of Execution
- The order of execution of a SELECT statement is as follows:
- FROM clause: locates the table that contains the data
- WHERE clause: restricts the rows to be returned
- SELECT clause: selects from the reduced data set the columns requested
- ORDER BY clause: orders the result set
Sorting with Multiple Columns
- It is also possible to sort query results by more than one column.
- In fact, there is no limit on how many columns you can add to the ORDER BY clause.
- An example of sorting with multiple columns is shown below.
- Employees are first ordered by department number (from lowest to highest), then for each department, the last names are displayed in alphabetical order (A to Z).
SELECT department_id, last_name
FROM employees
WHERE department_id <= 50
ORDER BY department_id, last_name;

- To create an ORDER BY clause to sort by multiple columns, specify the columns to be returned and separate the column names using commas.
- If you want to reverse the sort order of a column, add DESC after its name.
SELECT department_id, last_name
FROM employees
WHERE department_id <= 50
ORDER BY department_id DESC, last_name;

The ORDER BY for the department_id has been reversed (from the previous example) using DESC, so are now shown from highest to lowest, the order of last name is still alphabetical, A to Z.
Terminology
Key terms used in this tutorial included:
- ORDER BY Clause
- ASCENDING
- DESCENDING
- Order of Execution
Summary
In this tutorial, you should have learned how to:
- Construct a query to sort a result set in ascending or descending order
- Construct a query to order a result set using a column alias
- Construct a query to order a result set for single or multiple columns
0 Comments