Skip to content Skip to main navigation Skip to footer

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.

Note

The ORDER BY clause must be the last clause of the SQL statement.

SELECT last_name, hire_date
FROM employees
ORDER BY hire_date;
ORDER BY Clause

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;
Sorting in Descending Order

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";
Using Column Aliases

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;
Sorting with Other Columns

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;
Sorting with Multiple Columns
  • 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;
Sorting with Multiple Columns

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

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *