MEPX
Chapter 4 of 10All chapters
  1. 1What SQL is
  2. 2SELECT and WHERE
  3. 3NULL
  4. 4Sorting and limiting
  5. 5Aggregates and GROUP BY
  6. 6Joins
  7. 7Keys and relationships
  8. 8Subqueries and CTEs
  9. 9Indexes and performance
  10. 10Changing data

Chapter 4 of 10

Sorting and limiting

ORDER BY, LIMIT and paging.

Order is not free

Rows come back in no guaranteed order unless you ask. ORDER BY sorts ascending by default and DESC reverses it, and you can sort by several columns in turn.

  • LIMIT caps the number of rows and OFFSET skips some, which is how paging is written.
  • Deep OFFSET is slow, because the database still walks the rows it skips.

Stable paging

Sorting by a non-unique column makes paging unreliable, since equal rows can swap places between queries. Add a unique tiebreaker such as the id.