ORDER BY in PostgreSQL

When you run a SELECT query, PostgreSQL does not guarantee any particular row order unless you explicitly request one. The ORDER BY clause controls the sort sequence of the result set before it is returned to the client. Row order without ORDER BY is arbitrary and can change between executions as the query planner chooses different execution paths.

Syntax

SELECT select_list
FROM table_name
ORDER BY sort_expression1 [ASC | DESC] [NULLS FIRST | NULLS LAST],
         sort_expression2 [ASC | DESC] [NULLS FIRST | NULLS LAST];

ASC (ascending) is the default and can be omitted. Sort expressions can be column names, column positions, expressions, or aliases defined in the SELECT list.

Practical Example

Create a sample events table and explore ORDER BY behaviours:

CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  title       VARCHAR(200) NOT NULL,
  venue       VARCHAR(100),
  capacity    INT,
  starts_at   TIMESTAMPTZ,
  ticket_price NUMERIC(8, 2)
);

INSERT INTO events (title, venue, capacity, starts_at, ticket_price) VALUES
  ('Cloud Summit',      'Convention Center', 2000, '2026-09-15 09:00+00', 299.00),
  ('DevOps Day',        'Tech Hub',          500,  '2026-06-20 10:00+00',  99.00),
  ('Data Workshop',     'Innovation Lab',    80,   '2026-05-10 14:00+00',  49.00),
  ('Keynote Webinar',   NULL,                NULL, '2026-04-30 17:00+00',   0.00),
  ('Architecture Deep', 'Tech Hub',          120,  '2026-08-05 09:00+00', 149.00);

Sort by a single column ascending (default):

SELECT title, starts_at
FROM events
ORDER BY starts_at;

Sort descending:

SELECT title, ticket_price
FROM events
ORDER BY ticket_price DESC;

Sort by multiple columns:

PostgreSQL applies sort expressions left to right. Rows sharing the same venue are further sorted by starts_at:

SELECT title, venue, starts_at
FROM events
ORDER BY venue ASC, starts_at ASC;

Sort by an expression or alias:

Because ORDER BY is evaluated after SELECT, column aliases are available:

SELECT
  title,
  capacity,
  ticket_price,
  capacity * ticket_price AS potential_revenue
FROM events
ORDER BY potential_revenue DESC NULLS LAST;

Control NULL placement:

-- NULLs appear last (default for ASC)
SELECT title, venue FROM events ORDER BY venue ASC NULLS LAST;

-- Push NULLs to the top
SELECT title, venue FROM events ORDER BY venue ASC NULLS FIRST;

-- NULLs appear last even when sorting DESC
SELECT title, capacity FROM events ORDER BY capacity DESC NULLS LAST;

Verify sort with psql:

\x
SELECT title, venue, capacity FROM events ORDER BY capacity DESC NULLS LAST;
\x

NULL Handling Reference

Consistent NULL placement is important for pagination — a mix of NULL and non-NULL values in the sort key can cause rows to appear on multiple pages or be skipped if placement changes between queries.

Testing with Vela

Sort order bugs in paginated APIs are notoriously hard to reproduce in development because they depend on actual data distribution. Vela’s database branching lets you clone your production database, run queries with realistic data volumes, and verify that ORDER BY + LIMIT/OFFSET produces the expected results — including edge cases around NULL values and tie-breaking — before promoting any query change to production.

Production Tips

• Always pair LIMIT or OFFSET pagination with ORDER BY. Without a stable sort, the same row can appear on two pages or be skipped entirely across requests.
• Add a tiebreaker column (ORDER BY created_at DESC, id DESC) when the primary sort column is not unique — this ensures deterministic results.
• Large OFFSET values are expensive: PostgreSQL must scan and discard all preceding rows. For high-volume pagination, prefer keyset pagination (WHERE id > $last_id ORDER BY id).
• An index on the sort column can allow PostgreSQL to return rows in order via an index scan, eliminating a separate sort step — check EXPLAIN output for Sort nodes.
• NULLS LAST on descending sorts is almost always what you want in application code — having NULL appear before real values at the top of a list surprises users.
• You can sort by a column that is not in the SELECT list, which is useful for sorting without exposing internal columns like timestamps or rank scores.