SQLite - ORDER By Clause

SQLite - Sorting Query Results with ORDER BY

The ORDER BY clause is used in a SELECT statement to sort the result set based on the values in one or more columns. You can sort the data in either ascending or descending order.

Syntax

SELECT column_list
FROM table_name
[WHERE condition]
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

• ASC: Sorts the results in ascending order (from smallest to largest). This is the default behavior if no order is specified.
• DESC: Sorts the results in descending order (from largest to smallest).

Single-Column Sorting

Let’s sort the COMPANY table by SALARY in descending order to see the highest-paid employees first.

SELECT NAME, SALARY FROM COMPANY ORDER BY SALARY DESC;

This produces the following result:

NAME        SALARY
----------  ----------
David       85000.0
Mark        65000.0
Kim         45000.0
Paul        20000.0
Teddy       20000.0
Allen       15000.0
James       10000.0

Multi-Column Sorting

You can sort by multiple columns. The result set is sorted by the first column, and then, for any rows with the same value in the first column, it sorts them by the second column, and so on. Let’s sort by AGE (ascending) and then by NAME (alphabetically) for employees of the same age.

SELECT NAME, AGE FROM COMPANY ORDER BY AGE ASC, NAME ASC;

Notice how Mark and Allen, both age 25, are now sorted alphabetically by name.

NAME        AGE
----------  ----------
Kim         22
Teddy       23
James       24
Allen       25
Mark        25
David       27
Paul        32

Handling NULL Values

By default, NULL values are considered smaller than any other value. This means they appear first in an ASC sort and last in a DESC sort. You can override this behavior using NULLS FIRST or NULLS LAST.

Imagine our SALARY column could have NULL values. To list employees with known salaries first (in descending order), followed by those with unknown salaries, you would use:

-- Assuming some employees have a NULL salary
SELECT NAME, SALARY FROM COMPANY ORDER BY SALARY DESC NULLS LAST;

Sorting by an Expression

You are not limited to sorting by columns. You can also sort by the result of an expression. For example, let’s find the employee with the highest salary-to-age ratio.

SELECT NAME, SALARY, AGE, (SALARY / AGE) AS RATIO
FROM COMPANY
ORDER BY RATIO DESC;

This powerful feature allows for complex sorting logic directly within your SQL query.