The ORDER BY clause in SQLite

The ORDER BY clause in SQLite is used to sort the result set of a query in either ascending or descending order based on one or more columns. Sorting the results allows you to organize data in a way that makes it easier to analyze or present, whether it’s sorting by a date, alphabetically by name, or numerically by a score.

Syntax of the ORDER BY Clause

SELECT columns
FROM table
WHERE condition
ORDER BY column_name [ASC | DESC];

• columns: The columns you want to select.
• table: The table from which to retrieve the data.
• condition: Optional filter using a WHERE clause to narrow down the results.
• column_name: The column by which to sort the results.
• ASC (ascending) or DESC (descending): Specifies the order of the sort. If not specified, the default is ascending.

Key Points

• ASC (ascending): Sorts from lowest to highest for numeric columns, or alphabetically from A to Z for text columns.
• DESC (descending): Sorts from highest to lowest for numeric columns, or alphabetically from Z to A for text columns.
• You can sort by one or more columns.

Example 1: Sorting in Ascending Order (Default)

By default, if no sort order is specified, the ORDER BY clause will sort results in ascending order.

For example, to retrieve all employees sorted by their age in ascending order:

SELECT * FROM employees
ORDER BY age;

This query will return all employees with their ages sorted from the youngest to the oldest.

Example 2: Sorting in Descending Order

To retrieve the employees sorted by their age in descending order (from oldest to youngest):

SELECT * FROM employees
ORDER BY age DESC;

This query will return all employees sorted by their age in descending order.

Example 3: Sorting by Multiple Columns

You can also sort by multiple columns. When sorting by multiple columns, SQLite will first sort by the first column specified, and if there are ties (i.e., multiple rows have the same value in the first column), it will use the second column to break the tie, and so on.

For example, if you want to sort employees first by their department, and then by their salary in descending order within each department:

SELECT * FROM employees
ORDER BY department, salary DESC;

This will first sort employees by department alphabetically and then, within each department, sort them by salary in descending order (highest to lowest).

Example 4: Sorting Alphabetically by Name

To retrieve a list of employees sorted alphabetically by their name in ascending order:

SELECT * FROM employees
ORDER BY name;

This will sort the employees alphabetically from A to Z by their name.

To reverse the order (Z to A):

SELECT * FROM employees
ORDER BY name DESC;

Example 5: Sorting with NULL Values

By default, when sorting with the ORDER BY clause, NULL values are considered the lowest values when sorting in ascending order, and the highest values when sorting in descending order.

For example, if you have a table of employees with some missing salary values (i.e., NULL):

SELECT * FROM employees
ORDER BY salary DESC;

This will place all employees with a NULL salary at the end of the list (because NULL is considered the lowest value in descending order).

If you want to sort NULL values last, you can use IS NULL in the ORDER BY clause to explicitly control their position.

Example 6: Using ORDER BY with LIMIT for Top N Results

You can combine the ORDER BY clause with the LIMIT clause to get the top N results, such as the highest-paid employees.

For example, to get the top 5 highest-paid employees:

SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;

This query will return the top 5 employees with the highest salaries.

Example 7: Sorting Dates

If you have a column of dates and you want to sort the data chronologically, you can use the ORDER BY clause to sort the dates either in ascending or descending order.

For example, to get employees sorted by their hire_date from the earliest to the most recent:

SELECT * FROM employees
ORDER BY hire_date ASC;

To sort them in reverse (from most recent to earliest):

SELECT * FROM employees
ORDER BY hire_date DESC;

Summary of ORDER BY Usage

• ORDER BY column_name: Sorts the data by the specified column in ascending order (default).
• ORDER BY column_name DESC: Sorts the data by the specified column in descending order.
• Multiple Columns: You can sort by multiple columns, and the data will be sorted by the first column, then by the second column (in case of ties), and so on.
• NULL values: By default, NULL values are sorted as the lowest in ascending order and as the highest in descending order.
• Pagination: Combine ORDER BY with LIMIT and OFFSET to control which subset of data you want to retrieve, such as when displaying paginated results.

Common Scenarios:

• Sorting by Age: ORDER BY age DESC;
• Sorting Alphabetically by Name: ORDER BY name ASC;
• Sorting by Multiple Criteria: ORDER BY department, salary DESC;
• Top N Results: ORDER BY salary DESC LIMIT 5;

The ORDER BY clause is a powerful tool for controlling the order of data in your query results, making it easier to find the highest or lowest values, or to organize the data in a meaningful way.