Db2 ORDER BY

Summary: in this tutorial, you will learn how to use the Db2 ORDER BY clause to sort the result of a query in ascending or descending order.

Introduction to Db2 ORDER BY clause

When you use the SELECT statement to query data from a table, the order of rows in the result set is unspecified.

To sort the result set by values in one or more columns, you use the ORDER BY clause.

The ORDER BY clause is an optional clause of the SELECT statement. It always appears at the end of the SELECT statement as follows:

SELECT 
    select_list
FROM 
    table_name
ORDER BY 
    expression1 [ASC | DESC],
    expression2 [ASC | DESC],
    ...;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify expression1, expression2, etc., that can be columns or expressions by which you want to sort the result set.
• Second, use ASC to sort the result set in ascending order (from low to high) and DESC to sort the result set in descending order (from high to low).

The ASC or DESC is optional. If you skip it, Db2 will use ASC by default.

When evaluating the SELECT statement with an ORDER BY clause, Db2 evaluates the clauses in the following order: FROM, SELECT, and ORDER BY. In other words, Db2 always evaluates the ORDER BY clause at last.

Db2 ORDER BY clause examples

We will use the books table from the sample database to demonstrate the ORDER BY clause.

1) Using Db2 ORDER BY clause to sort a result set by values in one column example

This example returns titles, total pages, and ratings of all books sorted by titles in ascending order:

SELECT
    title,
    total_pages,
    rating
FROM
    books
ORDER BY
    title;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

In this example, we did not specify ASC or DESC after the title column in the ORDER BY clause, therefore, Db2 sorted books by titles alphabetically.

2) Using Db2 ORDER BY clause to sort a result set by values in two columns example

The following example sorts books by the number of pages in descending order and titles in ascending order:

SELECT
    title,
    total_pages,
    rating
FROM
    books
ORDER BY
    rating DESC,
    title;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, sort all books by ratings from high to low to make an initial result set sorted by ratings.
• Second, sort the sorted result set by titles. It means that if two or more books that have the same ratings, the ORDER BY clause will sort those books by titles in ascending order.

The following picture shows the partial output:

3) Using Db2 ORDER BY clause to sort a result by results of an expression

The LENGTH() function returns the length of a string. This example uses the ORDER BY clause to sort the books by the length of the titles from long to short:

SELECT
    title
FROM
    books
ORDER BY
    LEN(title) DESC;
Code language: SQL (Structured Query Language) (sql)

Here is the partial result set:

4) Using Db2 ORDER BY clause to sort a result set by ordinal positions of columns

It is possible to use the ordinal positions of columns in the select list in the ORDER BY clause for sorting the result set.

The following example sorts the books by the lengths of their titles. However, instead of using the expression LENGTH(title) explicitly in the ORDER BY clause, it uses the ordinal positions of the expression:

SELECT
    book_id,
    title,
    LENGTH(title)
FROM
    books
ORDER BY
    3;
Code language: SQL (Structured Query Language) (sql)

Here is the partial output:

It is a good practice to avoid the ordinal positions of columns in the ORDER BY clause. Because using the ordinal positions of columns in the ORDER BY clause makes the query difficult to maintain and may cause bugs if you forget to change the ordinal positions in the ORDER BY clause when you modify the select list.

Db2 ORDER BY clause with NULL values

The NULL values are special. They are the markers indicating missing values. When you sort a list of values that consists of NULL values, you can specify whether to treat NULL values as the lowest or highest values by using the NULLS FIRST or NULLS LAST option:

ORDER BY expression [NULLS FIRST | NULLS LAST]Code language: SQL (Structured Query Language) (sql)

The following query returns the title and ISBN of books. It treats NULL values as the lowest values:

SELECT 
	title, 
	isbn
FROM 
	books
ORDER BY 
	isbn NULLS FIRST;Code language: SQL (Structured Query Language) (sql)

The following query treats NULL values as the highest values by using the NULLS LAST option in the ORDER BY clause:

SELECT 
	title, 
	isbn
FROM 
	books
ORDER BY 
	isbn NULLS LAST;Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Db2 ORDER BY clause to sort a result set by values in one or more columns.