ORDER BY

Introduction

ORDER BY sorts the result. By default ascending; use DESC for descending. You can sort by multiple columns and by computed expressions.

Understanding the topic

Core concepts to understand:

• ORDER BY created_at DESC — newest first.
• ORDER BY country, name — group by country, then alphabetize.
• Use NULLS FIRST/LAST in Postgres to control NULL position.
• Sorting big sets is expensive — back it with an index.
• Pair with LIMIT for top-N queries.

Syntax reference

Visual workflow / architecture:

unsorted rows
        │
        ▼
   in-memory or disk sort
        │
        ▼
sorted rows
   ──── LIMIT 10 ────▶ top results

Indexed sort:  rows already in order  → instant

SELECT <cols>
FROM   <table>
ORDER BY <col1> [ASC|DESC] [, <col2> ...]
        [NULLS FIRST|LAST];

SELECT name, score
FROM   players
ORDER  BY score DESC, name ASC;

Real-world use

Twitter timelines, news feeds, leaderboards and 'recent orders' tables are all ORDER BY + LIMIT. With proper indexes, even billion-row tables answer in milliseconds.

Best practices

• Always have an index that matches the ORDER BY for big tables.
• Order by stable, indexed columns; avoid ORDER BY random() in hot paths.
• Use LIMIT with ORDER BY — never sort entire tables for a UI.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Default direction of ORDER BY.
• Q2. How to put NULLs last in Postgres.
• Q3. Why is sorting expensive without indexes?
• Q4. ORDER BY 1, 2 — what does it mean?
• Q5. Top-N pattern.