How do you write row limiting in standard SQL instead of LIMIT and OFFSET?

## Two spellings for one idea Every mainstream engine can return a contiguous slice of an ordered result, but they did not agree on syntax first and standardise later. Two families exist: ```sql -- widely implemented extension SELECT ... ORDER BY id LIMIT 10 OFFSET 20; -- ISO row-limiting clause SELECT ... ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY; ``` Both mean: sort by `id`, discard 20 rows, return at most the next 10. The standard clause arrived in SQL:2008; `LIMIT` predates it by many years and is not part of ISO SQL, which is why it is called an extension even though it is the form most developers type. ## Anatomy of the standard clause The clause has two independent, optional parts, and they must appear in this order after `ORDER BY`: 1. **`OFFSET <count> ROWS`** — skip `<count>` rows of the ordered result. 2. **`FETCH { FIRST | NEXT } [<count>] { ROW | ROWS } ONLY`** — return at most `<count>` of the rows that remain. The keyword pairs are synonyms, present purely for readability: - `FIRST` and `NEXT` are equivalent. `FETCH FIRST` reads naturally on its own; `FETCH NEXT` reads naturally after an `OFFSET`. Neither continues from a previous statement — there is no hidden cursor state, and every execution starts from the beginning of a freshly computed result. - `ROW` and `ROWS` are equivalent, so you can write `OFFSET 1 ROW` without grammatical pain. - The count may be omitted, in which case it defaults to one: `FETCH FIRST ROW ONLY` returns a single row. `ONLY` is the terminator that says "stop at exactly that many rows". Its alternative, `WITH TIES`, changes the semantics by also returning rows that tie with the last one on the `ORDER BY` key — a different clause with a different meaning. ## Using either half alone ```sql -- top-10, no skipping SELECT customer_id, SUM(total) AS lifetime FROM orders GROUP BY customer_id ORDER BY lifetime DESC, customer_id FETCH FIRST 10 ROWS ONLY; -- skip the header rows of a staging load, keep the rest SELECT * FROM raw_import ORDER BY line_no OFFSET 3 ROWS; ``` The second form — an offset with no fetch — is valid standard SQL, but engines differ on whether they accept a skip with no row-count limit at all, so it is the least portable shape of the four. ## Ordering is still your responsibility Neither spelling creates an order. `FETCH FIRST 10 ROWS ONLY` on an unordered query returns ten arbitrary rows, exactly as `LIMIT 10` does, and a sort key with duplicates leaves the page boundary undefined in both. Some engines go further and *require* an `ORDER BY` before the standard clause; others allow it without. Write the total ordering — leading key plus a unique tie-breaker — regardless of which engine you are on, because the determinism you want comes from the ordering, not from the limiting syntax. ## Which one should you actually write? - If the SQL lives inside one application against one known engine, use whichever form that engine documents; `LIMIT` is shorter and universally understood by readers. - If the SQL must run against several engines — a portable schema tool, an analytics query shipped to customers, generated SQL — prefer the standard clause and verify each target, because "standard" here does not mean "universally accepted". Two very common engines (MySQL and SQLite) implement only `LIMIT`/`OFFSET`, while PostgreSQL, Oracle 12c and later, and SQL Server 2012 and later implement the standard clause; PostgreSQL accepts both. - If you are generating SQL programmatically, keep the limiting clause behind a per-dialect renderer rather than string-formatting one spelling everywhere. It is the single most dialect-divergent piece of an otherwise portable `SELECT`. ## What an interviewer is checking The question is rarely about memorising syntax. It checks that you know `LIMIT` is not standard SQL, that you can name the ISO form and place it after `ORDER BY`, and that you understand the clause is optional in both halves. A candidate who adds "and it still needs a total ORDER BY to be meaningful" has answered the question behind the question.