SQLite ORDER BY: Sort Rows ASC, DESC, and by Multiple Columns

Without ORDER BY, Row Order Is Undefined

A SELECT without ORDER BY returns rows in whatever order SQLite finds convenient. It often looks like insertion order on a small table, which lulls people into trusting it. Don't. The moment an index gets used, or the table grows, or a query plan changes, that order can shift without warning.

If you care about the order of your rows, say so explicitly:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country FROM users ORDER BY name;

ORDER BY name sorts ascending by default. The result comes back Ada, Boris, Chen, Rosa — alphabetically, every time, regardless of how the table is stored on disk.

ASC and DESC

ASC means ascending (smallest to largest, A to Z, oldest to newest). DESC means descending — the reverse. ASC is the default, so you'll almost always see it left out:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   '2024-01-05'),
    ('Rosa',  '2024-02-20'),
    ('Chen',  '2024-03-01');

SELECT name, signup_date FROM users ORDER BY signup_date DESC;

That gives you the most recent signups first. Dates stored as ISO 8601 strings (YYYY-MM-DD) sort correctly as text — that's one of the reasons the format is preferred for date columns in SQLite, which has no dedicated date type.

Sorting by Multiple Columns

When the first sort column has ties, SQLite uses the second column to break them. List columns comma-separated, in priority order:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT
);

INSERT INTO users (name, country) VALUES
    ('Boris', 'FR'),
    ('Ada',   'US'),
    ('Rosa',  'FR'),
    ('Chen',  'US');

SELECT name, country FROM users ORDER BY country, name;

Rows are grouped by country first (FR before US), and within each country, sorted by name. Each column can carry its own direction:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    country TEXT,
    signup_date TEXT
);

INSERT INTO users (name, country, signup_date) VALUES
    ('Boris', 'FR', '2024-03-12'),
    ('Ada',   'US', '2024-01-05'),
    ('Rosa',  'FR', '2024-02-20'),
    ('Chen',  'US', '2024-03-01');

SELECT name, country, signup_date
FROM users
ORDER BY country ASC, signup_date DESC;

Country ascending, then most-recent-first within each country. The ASC and DESC apply to the column they sit next to — they don't carry over.

Sorting by Expressions and Aliases

ORDER BY accepts any expression, not just column names. Useful for computed values:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    quantity INTEGER
);

INSERT INTO products (name, price, quantity) VALUES
    ('Notebook', 4.50, 30),
    ('Pen',      1.20, 100),
    ('Lamp',    25.00, 5);

SELECT name, price * quantity AS revenue
FROM products
ORDER BY revenue DESC;

The alias revenue from the SELECT list is fair game in ORDER BY. You can also write the expression out again — ORDER BY price * quantity DESC — and it works the same way.

You can also sort by column position, though it's a habit worth avoiding:

SELECT name, price FROM products ORDER BY 2 DESC;

2 means the second column in the select list. It works, but if anyone reorders the columns later, the sort silently changes meaning. Sort by name or alias instead.

Where NULLs Land

NULL is "unknown," and SQLite has to decide where unknown values belong in a sort. The default rule: NULLs come first in ASC, last in DESC.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users ORDER BY signup_date;

Ada and Chen show up at the top, before any real date. That's rarely what you want for "most recent first." Override with NULLS LAST:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    signup_date TEXT
);

INSERT INTO users (name, signup_date) VALUES
    ('Boris', '2024-03-12'),
    ('Ada',   NULL),
    ('Rosa',  '2024-02-20'),
    ('Chen',  NULL);

SELECT name, signup_date FROM users
ORDER BY signup_date DESC NULLS LAST;

Now real dates come first, NULLs at the bottom. NULLS FIRST does the opposite. Both are part of standard SQL and work in SQLite 3.30 and later.

Case-Insensitive Sorting with COLLATE NOCASE

SQLite's default text comparison is binary — it sorts by Unicode code points. That means uppercase letters sort before lowercase ones, so 'Zoe' comes before 'apple':

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name;

The result is Boris, Zoe, ada, apple — uppercase first, then lowercase. To sort case-insensitively, attach the NOCASE collation:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

INSERT INTO users (name) VALUES ('apple'), ('Zoe'), ('Boris'), ('ada');

SELECT name FROM users ORDER BY name COLLATE NOCASE;

Now you get ada, apple, Boris, Zoe. NOCASE only treats ASCII A–Z and a–z as equivalent — it doesn't normalize accents or non-ASCII letters. For real internationalized sorting, you'd need an application-level collation, but NOCASE is fine for the common English case.

Random Order

Occasionally you want rows in random order — picking a daily featured item, sampling rows for testing. SQLite's random() function returns a random integer; sort by it:

CREATE TABLE quotes (id INTEGER PRIMARY KEY, text TEXT);

INSERT INTO quotes (text) VALUES
    ('Make it work, make it right, make it fast.'),
    ('Premature optimization is the root of all evil.'),
    ('Simple is better than complex.'),
    ('Readability counts.');

SELECT text FROM quotes ORDER BY random() LIMIT 1;

Each row gets a fresh random value, and the sort shuffles them. Fine for small tables. On big tables, ORDER BY random() is slow — it has to compute a random value for every row and sort the entire result. For sampling a single row from a huge table, fancier approaches (picking a random rowid, for example) are faster.

Common Pitfalls

A few things that bite people:

• Forgetting ORDER BY and assuming order. Without it, the order is undefined. Even if it looks stable, it isn't.
• Sorting numbers stored as text. '10' sorts before '2' lexicographically. If a column should sort numerically, store it with a numeric affinity (or cast: ORDER BY CAST(value AS INTEGER)).
• Mixing ASC and DESC across columns. Each column has its own direction. ORDER BY a, b DESC sorts a ascending and b descending, not both descending.
• Sorting a huge result set just to take the top few rows. Pair ORDER BY with LIMIT and put an index on the sort column — that's the next page.

Next: LIMIT and OFFSET

Sorting tells SQLite how to arrange rows; LIMIT and OFFSET tell it how many to return and where to start. Together they're the core of pagination and "top N" queries — coming up next.