Chapter 3 · 5 levels

Sorting and limits

Put results in a useful order with ORDER BY, break ties, take the top N with LIMIT, page with OFFSET and control where NULLs land.

Start

“Top 10 products”, “the 5 most recent orders”, “page 2 of the list”: half the requests you get involve an order. Without ORDER BY, a database promises nothing about the order of rows, so any ranked list needs one.

This chapter covers sorting by one or several columns, why every top-N query needs a tie-breaker, LIMIT and OFFSET, and the surprisingly inconsistent rules for where NULLs end up in each database. The boss level builds three homepage merchandising lists.

Levels

  1. Price list, most expensive first

    from Jessica Turner, Merchandising Manager

    order by, desc, tie breaker

    Code

  2. The 10 most recent orders

    from Rachel Adams, Customer Support Lead

    limit, top n, order by

    Code

  3. Page two of the price list

    from Ashley Rivera, Web Content Lead

    offset, pagination, limit

    Code

  4. Missing costs on top

    from Brian Mitchell, Finance Analyst

    order by, nulls first, null

    Code

  5. Boss: homepage merchandising lists

    from Ashley Rivera, Web Content Lead

    order by, limit, offset, tie breaker, where

    Boss · 3 steps