SQLite ORDER BY: Sort Query Results (Tested Examples)

Ninad Pathak

October 7, 2026

Without ORDER BY, SQLite doesn’t promise any order, so rows that come back sorted today can come back differently after a few inserts. In this article I’ll walk you through sorting results with ORDER BY, from a simple ascending sort to NULLs and numbers stored as text.

TLDR

SELECT … ORDER BY column; sorts ascending. Add DESC for descending.
List several columns to break ties: ORDER BY year DESC, author.

NULLs sort first in ascending order. Add NULLS LAST to move them to the end.
Text sorts with upper case before lower case. Use ORDER BY title COLLATE NOCASE for alphabetical order that ignores case.
Numbers stored as text sort as text (“10” before “9”). Sort by CAST(column AS INTEGER) instead.

What is ORDER BY in SQLite?

ORDER BY is the clause that sets the order of a query’s result rows. The SQLite SELECT docs are clear about what happens without it: “the order in which the rows are returned is undefined”.

SQLite ORDER BY syntax

SELECT columns
FROM table_name
[WHERE condition]
ORDER BY expression [COLLATE collation] [ASC | DESC] [NULLS FIRST | NULLS LAST], ...
[LIMIT n [OFFSET m]];

Part	What it does
`expression`	A column, a column alias, a column number (1 is the first selected column), or any expression
`ASC` / `DESC`	Ascending (the default) or descending
`NULLS FIRST` / `NULLS LAST`	Where NULLs go. By default they’re the smallest value
`COLLATE`	How text is compared, for example `NOCASE` to ignore case

How to sort query results

To show you how this works, I will use a small books table:

sqlite> CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author TEXT NOT NULL, year INTEGER, rating REAL, edition TEXT);
sqlite> INSERT INTO books (title, author, year, rating, edition) VALUES ('SQL Antipatterns', 'Karwin', 2010, 4.3, '2'), ('The Pragmatic Programmer', 'Hunt', 1999, 4.4, '20'), ('Designing Data-Intensive Applications', 'Kleppmann', 2017, 4.7, '1'), ('database internals', 'Petrov', 2019, 4.5, '1'), ('Working Effectively with Legacy Code', 'Feathers', 2004, NULL, '9'), ('Refactoring', 'Fowler', 1999, 4.6, '10');

Step 1: Sort ascending

sqlite> SELECT title, year FROM books ORDER BY year;
title                                  year
-------------------------------------  ----
The Pragmatic Programmer               1999
Refactoring                            1999
Working Effectively with Legacy Code   2004
SQL Antipatterns                       2010
Designing Data-Intensive Applications  2017
database internals                     2019

The first two books are both from 1999. Their order relative to each other isn’t guaranteed, so add a second column when ties matter.

Step 2: Sort descending with DESC

sqlite> SELECT title, rating FROM books ORDER BY rating DESC;
title                                  rating
-------------------------------------  ------
Designing Data-Intensive Applications  4.7
Refactoring                            4.6
database internals                     4.5
The Pragmatic Programmer               4.4
SQL Antipatterns                       4.3
Working Effectively with Legacy Code   NULL

Examples

These examples all use the same books table.

Sort by more than one column

The second column only breaks ties in the first. Here the 1999 books are sorted by author:

sqlite> SELECT year, author, title FROM books ORDER BY year DESC, author ASC;
year  author     title
----  ---------  -------------------------------------
2019  Petrov     database internals
2017  Kleppmann  Designing Data-Intensive Applications
2010  Karwin     SQL Antipatterns
2004  Feathers   Working Effectively with Legacy Code
1999  Fowler     Refactoring
1999  Hunt       The Pragmatic Programmer

Sort alphabetically, ignoring case

The default sort compares character codes, so every upper-case letter comes before every lower-case one, and database internals lands last. COLLATE NOCASE gives the alphabetical order you’d expect:

sqlite> SELECT title FROM books ORDER BY title;
title
-------------------------------------
Designing Data-Intensive Applications
Refactoring
SQL Antipatterns
The Pragmatic Programmer
Working Effectively with Legacy Code
database internals
sqlite> SELECT title FROM books ORDER BY title COLLATE NOCASE;
title
-------------------------------------
database internals
Designing Data-Intensive Applications
Refactoring
SQL Antipatterns
The Pragmatic Programmer
Working Effectively with Legacy Code

Put NULLs last

In ascending order, NULL comes first. NULLS LAST moves it to the end (SQLite 3.30.0 and later):

sqlite> SELECT title, rating FROM books ORDER BY rating LIMIT 2;
title                                 rating
------------------------------------  ------
Working Effectively with Legacy Code  NULL
SQL Antipatterns                      4.3
sqlite> SELECT title, rating FROM books ORDER BY rating NULLS LAST LIMIT 2;
title                     rating
------------------------  ------
SQL Antipatterns          4.3
The Pragmatic Programmer  4.4

Sort by column number or alias

ORDER BY 2 means the second selected column. An alias from the select list works too:

sqlite> SELECT author, year FROM books ORDER BY 2 DESC, 1;
author     year
---------  ----
Petrov     2019
Kleppmann  2017
Karwin     2010
Feathers   2004
Fowler     1999
Hunt       1999

sqlite> SELECT title, 2026 - year AS age FROM books ORDER BY age LIMIT 3;
title                                  age
-------------------------------------  ---
database internals                     7
Designing Data-Intensive Applications  9
SQL Antipatterns                       16

Sort by an expression

Any expression works, such as the length of the title:

sqlite> SELECT title FROM books ORDER BY length(title) LIMIT 2;
title
----------------
Refactoring
SQL Antipatterns

Custom sort order with CASE

A CASE expression puts chosen rows first. Here Fowler’s book comes first, then the rest by year:

sqlite> SELECT title, year FROM books ORDER BY CASE WHEN author = 'Fowler' THEN 0 ELSE 1 END, year;
title                                  year
-------------------------------------  ----
Refactoring                            1999
The Pragmatic Programmer               1999
Working Effectively with Legacy Code   2004
SQL Antipatterns                       2010
Designing Data-Intensive Applications  2017
database internals                     2019

Top N rows with LIMIT

ORDER BY runs before LIMIT, so this returns the second- and third-highest ratings:

sqlite> SELECT title, rating FROM books ORDER BY rating DESC LIMIT 2 OFFSET 1;
title               rating
------------------  ------
Refactoring         4.6
database internals  4.5

Sort by an aggregate

After GROUP BY, you can sort by the count:

sqlite> SELECT year, count(*) AS books FROM books GROUP BY year ORDER BY books DESC, year;
year  books
----  -----
1999  2
2004  1
2010  1
2017  1
2019  1

Pick random rows

ORDER BY random() shuffles the rows. The rows differ on each run, so this counts them instead of showing them:

sqlite> SELECT count(*) AS picked FROM (SELECT title FROM books ORDER BY random() LIMIT 2);
picked
------
2

Edge cases

Each of these comes from the test run.

Numbers stored as text

The edition column is TEXT, so “10” sorts before “2”. Casting to a number fixes the order:

sqlite> SELECT title, edition FROM books ORDER BY edition;
title                                  edition
-------------------------------------  -------
Designing Data-Intensive Applications  1
database internals                     1
Refactoring                            10
SQL Antipatterns                       2
The Pragmatic Programmer               20
Working Effectively with Legacy Code   9
sqlite> SELECT title, edition FROM books ORDER BY CAST(edition AS INTEGER);
title                                  edition
-------------------------------------  -------
Designing Data-Intensive Applications  1
database internals                     1
SQL Antipatterns                       2
Working Effectively with Legacy Code   9
Refactoring                            10
The Pragmatic Programmer               20

Columns with mixed types

SQLite sorts by storage class first, following its datatype rules. NULL comes first and BLOBs come last, with numbers before text in between.

sqlite> SELECT v, typeof(v) AS type FROM (SELECT 'b' AS v UNION ALL SELECT 10 UNION ALL SELECT NULL UNION ALL SELECT 2.5 UNION ALL SELECT X'00' UNION ALL SELECT 'A') ORDER BY v;
v     type
----  -------
NULL  null
2.5   real
10    integer
A     text
b     text
      blob

Sorting large tables

Without an index, SQLite sorts the result in a temporary B-tree. With an index on the sort column, it reads the rows in index order and skips the sort:

sqlite> EXPLAIN QUERY PLAN SELECT title FROM books ORDER BY year;
QUERY PLAN
|--SCAN books
`--USE TEMP B-TREE FOR ORDER BY
sqlite> CREATE INDEX idx_books_year ON books(year);
sqlite> EXPLAIN QUERY PLAN SELECT title FROM books ORDER BY year;
QUERY PLAN
`--SCAN books USING INDEX idx_books_year

See SQLite CREATE INDEX for more on indexes.

Conclusion

Add ORDER BY to every query whose order matters, and add a tie-breaker column when values can repeat. Use COLLATE NOCASE for alphabetical lists and NULLS LAST to push missing values down. When numbers are stored as text, sort by CAST(column AS INTEGER).