SQLite: ORDER BY Clause

This SQLite post explains how to use the SQLite ORDER BY clause with syntax and examples.

Description

The SQLite ORDER BY clause is used to sort the records in your result set.

Syntax

The syntax for the ORDER BY clause in SQLite is:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. The conditions that must be met for the records to be selected.

ASC

Optional. It sorts the result set in ascending order by expression. ASC is the default, if no modifier is provider.

DESC

Optional. It sorts the result set in descending order by expression.

Note

• If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order. This is equivalent to ORDER BY expression ASC.
• The ORDER BY clause can be used in a SELECT statement and SELECT LIMIT statement in SQLite.

Example - Sorting without using ASC/DESC attribute

The SQLite ORDER BY clause can be used without specifying the ASC or DESC modifier. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.

For example:

SELECT last_name, first_name
FROM employees
WHERE employee_id  10;
ORDER BY last_name;

This SQLite ORDER BY example would return all records from the employees tables where the employee_id is less than 10. The result set would sorted by the last_name field in ascending order and would be equivalent to the following ORDER BY clause:

SELECT last_name, first_name
FROM employees
WHERE employee_id  10;
ORDER BY last_name ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

Example - Sorting in descending order

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:

SELECT employee_id, last_name, first_name
FROM employees
WHERE last_name = 'Mark'
ORDER BY first_name DESC;

This SQLite ORDER BY example would sort the result set by the first_name field in descending order.

Example - Sorting by relative position

You can also use the SQLite ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

For example:

SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id > 45
ORDER BY 3 DESC;

This SQLite ORDER BY would return the result set sorted by the first_name field in descending order, since the first_name field is in position #3 in the result set and would be equivalent to the following ORDER BY clause:

SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id > 45
ORDER BY first_name DESC;

Example - Using both ASC and DESC attributes

When sorting your result set using the SQLite ORDER BY clause, you can combine the ASC and DESC attributes in a single SELECT statement.

For example:

SELECT employee_id, last_name, first_name
FROM employees
WHERE employee_id > 45
ORDER BY last_name ASC, first_name DESC;

This SQLite ORDER BY would sort the result set by first the last_name field in ascending order, with a secondary sort on the first_name field in descending order.