beginner

ORDER BY Clause in SQL

By DataPlayArena Technical Review BoardUpdated 2026-07-208 min readRuntime Verified

Overview

The ORDER BY clause sorts the final rows returned by a query in ascending (ASC) or descending (DESC) order. In relational database theory based on set mathematics, tables are unordered sets of tuples. Without an explicit ORDER BY clause, the database engine makes zero guarantees regarding the physical order of returned rows—even if rows appear to return in inserted or indexed order during development.

What You Will Learn

In this lesson, you will master:
  • Relational Unordered Principle: Why ORDER BY is the only way to guarantee row sequence.
  • Multi-Column Sorting: Prioritizing primary, secondary, and tertiary sorting columns.
  • NULL Sorting Semantics: Controlling whether NULL values appear first or last with NULLS FIRST and NULLS LAST.
  • Sort Memory & Spilling: How query engines handle in-memory sorts vs spilling to temporary disk files (tempdb / work_mem).
  • Index-Backed Ordering: Eliminating physical sort steps by leveraging pre-sorted B-Tree indexes.

Basic & Multi-Column Sorting

When sorting by multiple columns, the database sorts rows by the first column. For rows with identical values in the first column, it resolves ties using the second column:

sql
SELECT employee_id, department, last_name, salary
FROM employees
ORDER BY 
    department ASC,     -- Group employees by department alphabetically
    salary DESC,         -- Within each department, sort highest earners first
    last_name ASC;       -- If salaries are identical, sort by last name alphabetically
Default Sort Direction

If you omit the sort direction keyword, SQL defaults to ASC (Ascending order: lowest numbers first, alphabetical A $\rightarrow$ Z, earliest dates first).


Controlling NULL Placement: NULLS FIRST vs NULLS LAST

By default, relational database engines differ in where they place NULL values when sorting:

  • PostgreSQL / Oracle: Treats NULL as larger than any value (ASC puts NULLs last; DESC puts NULLs first).
  • MySQL / SQL Server: Treats NULL as smaller than any value (ASC puts NULLs first; DESC puts NULLs last).

The ANSI SQL standard provides explicit syntax to guarantee deterministic placement regardless of database engine:

sql
-- Scenario: Find products with the highest discounts first,
-- but place products with NULL discounts at the very end
SELECT product_id, product_name, discount_percent
FROM products
ORDER BY discount_percent DESC NULLS LAST;

-- Ascending sort placing NULLs at the beginning
SELECT customer_id, company_name, referral_code
FROM customers
ORDER BY referral_code ASC NULLS FIRST;

How Database Engines Execute Sorts: Index Sort vs Sort Spill

Sorting is one of the most CPU- and memory-intensive operations in a database. The database engine executes sorting using one of two strategies:

1. Index-Backed Sort (Zero Cost)

If a B-Tree index exists on the sorting columns matching the query direction:

sql
CREATE INDEX idx_orders_ordered_at ON orders(ordered_at DESC);

The database traverses the index leaf nodes sequentially. No physical sorting occurs in memory! The query reads pre-sorted data directly from the index.

2. Physical Sort & Memory Spilling

If no matching index exists, the engine loads all matching rows into an in-memory buffer (PostgreSQL work_mem, SQL Server Sort Workspace).

  • If data size is smaller than allocated buffer: Sort is performed in memory using quicksort or mergesort (In-Memory Sort).
  • If data size exceeds allocated memory: The engine must write temporary chunks to disk and perform an external merge sort (Spill to Disk / Tempdb). This causes an immediate 10x–50x performance degradation!
sql
-- Checking execution plan for sort spills in PostgreSQL:
EXPLAIN ANALYZE
SELECT * FROM massive_table ORDER BY unindexed_column;
-- Look for: Sort Method: external merge  Disk: 45024kB -> BAD! Needs index or higher work_mem.

Sorting with Expressions & Positional Numbers

sql
-- 1. Sorting by Expression
SELECT full_name, (score_part1 + score_part2) AS total_score
FROM exam_results
ORDER BY (score_part1 + score_part2) DESC;

-- 2. Sorting by Alias (Allowed because ORDER BY executes after SELECT)
SELECT full_name, (score_part1 + score_part2) AS total_score
FROM exam_results
ORDER BY total_score DESC;

-- 3. Positional Sorting (Legal, but anti-pattern in production)
-- Sorts by the 2nd column in the SELECT list
SELECT full_name, total_score FROM exam_results ORDER BY 2 DESC;
Avoid Positional Sorting in Production

Writing ORDER BY 2 DESC makes queries fragile. If a developer later edits the SELECT list to add another column at position 2, the query silently sorts by the wrong column! Always sort by column name or alias.


Common Mistakes & Gotchas

  • Assuming Insertion Order Without ORDER BY: Relying on table storage order without ORDER BY leads to intermittent test and production bugs. Parallel table scans, background vacuuming, or deleted row page reuse causes rows to return in completely random order.
  • Sorting Massive Datasets Before LIMIT: Writing SELECT * FROM logs ORDER BY created_at DESC LIMIT 10 without an index on created_at forces the database to sort 50 million rows just to return the top 10! Always ensure sorting columns paired with LIMIT are backed by an index.

Interview Perspective

SQL Interview Question: How does the database engine handle ORDER BY under the hood, and how can you eliminate the sort step from the query plan?

Key Talking Points for Candidates:

  1. Execution Cost: Sorting requires gathering rows into memory buffers and running sorting algorithms. If the working set exceeds allocated memory (work_mem), it spills to disk, creating heavy I/O bottlenecks.
  2. Index Optimization: Creating a B-Tree index on the sort columns allows the engine to perform an Index Scan or Index Seek, completely eliminating the Sort node from the execution plan.
  3. Multi-Column Alignment: When sorting by ORDER BY A ASC, B DESC, a composite index must match the exact direction (CREATE INDEX idx ON t(A ASC, B DESC)) for optimal index-backed traversal.

Interactive Practice Challenge

Challenge: Top Performing Sales Representatives

Write a query on the sales_reps table that returns rep_name, region, and closed_deals_count. Sort the results primarily by region in ascending order, and within each region, sort by closed_deals_count in descending order, ensuring that any representatives with NULL closed deals appear at the bottom.

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.