The ORDER BY clause allows you to sort the results of a query based on a particular column.
Rows have no defined sort order in most databases by default. If no ORDER BY clause is specified, rows are returned based on speed and efficiency and should not be relied on to return rows in the same order every time.
To sort rows in ascending order use ORDER BY ... ASC and to sort in descending order use ORDER BY ... DESC.
The method by which a column is sorted depends on the data type of the column. By default in most database engines ASC will sort INTEGER types numerically from lowest to highest, TEXT types alphabetically, and DATE and similar types chronologically from oldest to newest. Refer to the particular database engine you use to see how other types are sorted.
It is best practice to always be explicit and include an ORDER BY in your queries. For brevity, the rest of the examples on this website will not include ORDER BY unless it's relevant to the example.
SELECT id, name_first, name_last, age
FROM users
ORDER BY age ASC;
ORDER BY is applied after other clauses like WHERE but there are some clauses that are applied after it such as LIMIT.
SELECT name_first, name_last, age
FROM users
WHERE age > 30
ORDER BY name_last ASC;
| name_first | name_last | age |
|------------|-----------|-----|
| Priya | Amini | 31 |
| Elena | Moreno | 40 |
| james | weber | 46 |
NULL values are sorted differently depending on the database engine used. SQLite treats NULL as "smaller" than non- NULL values, meaning they will appear first if you ORDER BY ... ASC.
Some database engines, including SQLite, support ASC NULLS LAST or DESC NULLS FIRST clauses which alter the default ordering of NULL in the results.
SELECT name_first, name_last, last_login
FROM users
ORDER BY last_login ASC;
Capitalized letters, special characters, whitespace and numbers inside strings have a particular sort order depending on the database engine. Be sure to find out the default ordering and comparison rules, or "collation", for the database you are using.
The default collation for text in SQLite is called BINARY which means text characters are compared based on their binary (ASCII/Unicode) representation. See ASCII Character Order.
This is why "james" appears at the bottom of the ASC ordered query.
SELECT name_first, email_address
FROM users
ORDER BY name_first ASC;
The COLLATE operator can be used to alter the collation rules for the current statement. SQLite includes three collation sequences:
RTRIM: Ignores trailing whitespace.
NOCASE: Ignores case.
BINARY: Compare text characters based on their binary (ASCII/Unicode) representation.
SQLite itself can be compiled with extensions such as language-specific collations. Other database engines like MySQL or PostgreSQL offer more built-in collations.
SELECT name_first, email_address
FROM users
ORDER BY name_first COLLATE NOCASE ASC;
Computations can be applied to an ORDER BY. This example shows a query that orders the table based on the total length of a user's first and last name.
length() is an unrelated function that is included to demonstrate the example.
SELECT name_first, name_last
FROM users
ORDER BY length(name_first) + length(name_last) ASC;