SQL LIMIT
Return a bounded slice after ORDER BY. SQLite, PostgreSQL, and MySQL use LIMIT; other dialects may use FETCH FIRST.
LIMIT slices an ordered result
8 eligible rowsOnly published courses are candidates for this catalog view.
stable tie-breakerThe key decides the order when ratings are equal.
first pageReturn the first three rows from the ordered result.
LIMIT chooses a slice after ordering
LIMIT 3 asks for at most three result rows. It does not mean the three best rows unless the query defines what best means with ORDER BY. The example keeps published, rated courses, ranks them by rating, and uses course_id to break a 4.8 tie. It should return Python APIs first, then SQL Foundations, then Intro to SQL.
Return the top three rated courses
Combine a clear filter, a complete order, and a three-row limit.
Edit the query, predict the rows it will return, then run it.
LIMIT is a common dialect form
This course runner is SQLite, which accepts LIMIT and OFFSET. PostgreSQL and MySQL use the same clauses. Standard SQL may instead write FETCH FIRST 3 ROWS ONLY, and some engines have still other pagination syntax. The idea is portable even when the keywords differ: choose a complete order, then take a bounded slice of that sequence.
OFFSET skips rows in the ordered result
LIMIT 3 OFFSET 3 skips the first three qualifying rows and returns the next three. Offset zero is the first page; offset three is the second page when each page holds three rows. The example sorts published courses by duration and ID, so the second page contains course IDs 101, 110, and 108. Keep the filter and full sort rule identical between pages.
Read the second page
Skip the first three published courses in a stable duration order.
Edit the query, predict the rows it will return, then run it.
Offset pages can move when data changes
Offsets are easy to understand, but they are not a snapshot. If a new row is inserted ahead of page two between requests, the page boundary shifts and a reader may see a duplicate or miss a row. Large offsets can also become expensive because the engine still walks the skipped rows. Later, you can use a cursor based on the last seen sort keys when a changing, large dataset needs more reliable pagination.
Common paging mistakes
Arbitrary top three
A limit bounds count, not meaning. Order the result before choosing its first rows.
Broken page sequence
Every page must use the same filter and complete ordering to describe one sequence.
Assuming a frozen catalog
Inserting a row ahead of page two can repeat or hide a course even when the query text is unchanged.
Pages that cannot be joined
Page two is defined as the next slice of the same ordered result, not a new ranking.
Independent lab: publish a ranked catalog page
Build a published Data-and-Web catalog ranked by rating. Put unrated courses last, break equal ratings by course_id, and show three courses per page. The starter query returns IDs 101, 110, and 102 on page one. Change OFFSET to 3 for page two; it should return IDs 103, 109, and 107.
Rank and page a published course feed
Combine filtering, explicit NULL placement, a unique tie-breaker, and a bounded page.
Edit the query, predict the rows it will return, then run it.
Lesson review
LIMIT returns at most a requested number of rows after the result is ordered. OFFSET skips rows from that ordered result. The slice is only a ranking when ORDER BY is complete, and offset pages can shift when the underlying data changes. SQLite, PostgreSQL, and MySQL share this syntax; other dialects may use FETCH FIRST.
- I can return a predictable page using ORDER BY, LIMIT, and OFFSET.
- I know LIMIT without ORDER BY is not a stable ranking.
- I keep the same filter and sort keys on every page.
- I understand why offset pages may shift when data changes.
- I can name LIMIT as SQLite, PostgreSQL, and MySQL syntax, with FETCH FIRST as a standard alternative.