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