ORDER BY — Sorting Query Results

Every useful query returns results in a meaningful order — newest posts first, highest-ranked products first, alphabetical user lists. Without ORDER BY, PostgreSQL returns rows in an unspecified, non-deterministic order that can change between queries. ORDER BY is also the prerequisite for reliable pagination: LIMIT and OFFSET only produce consistent pages when the full result set is sorted consistently. In FastAPI, every list endpoint that supports pagination must include ORDER BY in its underlying SQL.

Basic ORDER BY #

-- Sort ascending (default — A to Z, 1 to 100, oldest to newest)
SELECT id, title, created_at FROM posts ORDER BY created_at;
SELECT id, name FROM users ORDER BY name;   -- alphabetical

-- Sort descending (Z to A, 100 to 1, newest to oldest)
SELECT id, title, created_at FROM posts ORDER BY created_at DESC;
SELECT id, name, view_count FROM posts ORDER BY view_count DESC;

-- Sort by multiple columns (tiebreaker)
SELECT id, author_id, title, created_at
FROM posts
ORDER BY author_id ASC, created_at DESC;
-- Sort by author_id first (ascending);
-- within each author, sort by created_at (newest first)

-- Sort by column position (fragile — avoid in production)
SELECT id, name, email FROM users ORDER BY 2;   -- sorts by 2nd column (name)

-- Sort by alias
SELECT id, view_count * 2 AS double_views FROM posts
ORDER BY double_views DESC;

-- Sort by expression
SELECT id, title FROM posts
ORDER BY LENGTH(title) DESC;   -- longest titles first

NULL Handling in ORDER BY #

-- By default:
-- ASC order:  NULLs appear LAST (after all non-null values)
-- DESC order: NULLs appear FIRST (before all non-null values)

-- Explicitly control NULL position
SELECT id, published_at FROM posts
ORDER BY published_at DESC NULLS LAST;
-- Published posts (with a date) appear first, in descending order
-- Unpublished posts (NULL published_at) appear at the end

SELECT id, published_at FROM posts
ORDER BY published_at ASC NULLS FIRST;
-- NULL published_at appears first, then oldest published posts

ORDER BY in FastAPI Query Parameters #

-- Dynamic sorting: allow clients to choose sort field and direction
-- FastAPI endpoint: GET /posts?sort_by=created_at&order=desc

-- Safe approach: whitelist allowed sort fields in Python, then embed in SQL
-- In Python:
-- ALLOWED_SORT_FIELDS = {"created_at", "view_count", "title"}
-- if sort_by not in ALLOWED_SORT_FIELDS:
--     raise HTTPException(400, "Invalid sort field")
-- direction = "DESC" if order == "desc" else "ASC"

-- The resulting query:
SELECT id, title, view_count, created_at
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC, id DESC   -- always add id as final tiebreaker
LIMIT 10 OFFSET 0;

-- Cursor-based pagination (more efficient than OFFSET for large datasets)
-- "Give me posts created before the last post I saw" instead of skipping rows
SELECT id, title, created_at
FROM posts
WHERE status = 'published'
  AND (created_at, id) < ('2025-08-06 14:30:00', 42)   -- cursor tuple comparison
ORDER BY created_at DESC, id DESC
LIMIT 10;

Common Mistakes #

Mistake 1 — Paginating without ORDER BY #

❌ Wrong — inconsistent pages, rows can be skipped or duplicated:

SELECT id, title FROM posts LIMIT 10 OFFSET 10;   -- undefined order!

✅ Correct — always sort before paginating:

SELECT id, title FROM posts ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 10;

Mistake 2 — Sorting by non-unique column without a tiebreaker #

❌ Wrong — rows with the same created_at can shift between pages:

ORDER BY created_at DESC LIMIT 10 OFFSET 10

✅ Correct — add unique id as final tiebreaker:

ORDER BY created_at DESC, id DESC LIMIT 10 OFFSET 10   -- ✓ deterministic

Mistake 3 — Sorting by user input without validation (SQL injection risk) #

❌ Wrong — directly interpolating user-supplied column name:

query = f"SELECT * FROM posts ORDER BY {sort_field}"   # SQL injection!

✅ Correct — whitelist allowed sort fields:

ALLOWED = {"created_at", "view_count", "title"}
if sort_field not in ALLOWED:
    raise HTTPException(400, "Invalid sort field")
# Now safe to use sort_field in query ✓

Quick Reference #