SQLite ORDER BY

The ORDER BY clause sorts query results. Without it, SQLite returns rows in an unspecified order — the order depends on how data is stored internally and may change between queries. ORDER BY gives you consistent, predictable output.

Sample Table

books
────────────────────────────────────────────────────────────
id │ title              │ author    │ year │ pages │ rating
───┼────────────────────┼───────────┼──────┼───────┼───────
1  │ Deep Work          │ Newport   │ 2016 │ 296   │ 4.1
2  │ Atomic Habits      │ Clear     │ 2018 │ 320   │ 4.8
3  │ Thinking Fast Slow │ Kahneman  │ 2011 │ 499   │ 4.2
4  │ The Lean Startup   │ Ries      │ 2011 │ 336   │ 4.0
5  │ Zero to One        │ Thiel     │ 2014 │ 195   │ 4.3

Sort Ascending (Default)

SELECT title, rating FROM books ORDER BY rating;

title               │ rating
────────────────────┼───────
The Lean Startup    │ 4.0
Deep Work           │ 4.1
Thinking Fast Slow  │ 4.2
Zero to One         │ 4.3
Atomic Habits       │ 4.8

ASC (ascending) is the default. Lowest value appears first.

Sort Descending

SELECT title, rating FROM books ORDER BY rating DESC;

title               │ rating
────────────────────┼───────
Atomic Habits       │ 4.8
Zero to One         │ 4.3
Thinking Fast Slow  │ 4.2
Deep Work           │ 4.1
The Lean Startup    │ 4.0

Sort by Text Column

SELECT title, author FROM books ORDER BY author ASC;

title               │ author
────────────────────┼──────────
Atomic Habits       │ Clear
Thinking Fast Slow  │ Kahneman
Deep Work           │ Newport
The Lean Startup    │ Ries
Zero to One         │ Thiel

Text sorts alphabetically (A→Z for ASC, Z→A for DESC)

Sort by Multiple Columns

When two rows share the same value in the first sort column, SQLite uses the second column as a tiebreaker.

SELECT title, year, pages FROM books
ORDER BY year ASC, pages DESC;

title               │ year │ pages
────────────────────┼──────┼───────
Thinking Fast Slow  │ 2011 │ 499   ← same year, more pages first
The Lean Startup    │ 2011 │ 336   ← same year, fewer pages
Zero to One         │ 2014 │ 195
Deep Work           │ 2016 │ 296
Atomic Habits       │ 2018 │ 320

Sorting by Column Position

Instead of a column name, you can use its position number in the SELECT list.

SELECT title, rating FROM books ORDER BY 2 DESC;
-- "2" refers to the second column in SELECT (rating)

Sorting with NULL Values

In SQLite, NULL values sort as if they are smaller than any other value. They appear first in ASC order and last in DESC order.

SELECT title, rating FROM books ORDER BY rating ASC;

NULL rows appear FIRST in ASC:
──────────────────────────────────
│ NULL   │ ← first
│ 4.0    │
│ 4.1    │
│ 4.8    │

NULL rows appear LAST in DESC:
──────────────────────────────────
│ 4.8    │
│ 4.1    │
│ 4.0    │
│ NULL   │ ← last

ORDER BY with WHERE

-- Books published after 2012, sorted by rating
SELECT title, year, rating
FROM books
WHERE year > 2012
ORDER BY rating DESC;

title          │ year │ rating
───────────────┼──────┼───────
Atomic Habits  │ 2018 │ 4.8
Zero to One    │ 2014 │ 4.3
Deep Work      │ 2016 │ 4.1

ORDER BY with an Expression or Function

You can sort by the result of an expression or function, not just a plain column name.

-- Sort books by title length (shortest title first)
SELECT title, LENGTH(title) AS title_len
FROM books
ORDER BY LENGTH(title) ASC;

title               │ title_len
────────────────────┼──────────
Deep Work           │ 9
Zero to One         │ 11
Atomic Habits       │ 13
The Lean Startup    │ 16
Thinking Fast Slow  │ 18

-- Sort by the last word in title (author's surname alphabetically)
SELECT title, author
FROM books
ORDER BY LOWER(author);

title               │ author
────────────────────┼──────────
Atomic Habits       │ Clear
Thinking Fast Slow  │ Kahneman
Deep Work           │ Newport
The Lean Startup    │ Ries
Zero to One         │ Thiel

ORDER BY with CASE — Custom Sort Order

A CASE expression inside ORDER BY lets you define a custom sort priority that does not follow the natural alphabetical or numeric order.

-- Sort books by a custom rating tier: 4.5+ first, then 4.0–4.4, then rest
SELECT title, rating
FROM books
ORDER BY
  CASE
    WHEN rating >= 4.5 THEN 1
    WHEN rating >= 4.0 THEN 2
    ELSE 3
  END,
  rating DESC;

title               │ rating
────────────────────┼───────
Atomic Habits       │ 4.8   ← tier 1
Zero to One         │ 4.3   ← tier 2 (highest in tier)
Thinking Fast Slow  │ 4.2
Deep Work           │ 4.1
The Lean Startup    │ 4.0

ORDER BY with COLLATE — Text Collation

By default, SQLite text sorting is case-sensitive for ASCII text. Add COLLATE NOCASE to sort without regard to letter case.

TABLE: tags
──────────────
tag
──────────────
apple
Banana
cherry
Date

-- Default sort (case-sensitive): uppercase before lowercase
SELECT tag FROM tags ORDER BY tag;
Banana
Date
apple
cherry

-- Case-insensitive sort
SELECT tag FROM tags ORDER BY tag COLLATE NOCASE;
apple
Banana
cherry
Date

ORDER BY Position in Full Query

SELECT   → what columns to show
FROM     → which table
WHERE    → filter rows
GROUP BY → group rows
HAVING   → filter groups
ORDER BY → sort results   ← here
LIMIT    → cap row count

Key Takeaways

• ORDER BY column ASC sorts lowest to highest; DESC sorts highest to lowest
• Default sort direction is ASC when not specified
• Multiple sort columns separate by commas; the first column sorts first and ties break on the next
• NULL values sort before non-NULL values in ASC order and after them in DESC order
• You can sort by expressions, functions, or CASE statements — not just plain column names
• Add COLLATE NOCASE to make text sorting case-insensitive
• ORDER BY goes after WHERE and GROUP BY but before LIMIT in the query