SQL Dialects Reference/Select queries/Limiting results of select query

From Wikibooks, the open-content textbooks collection

[edit] Limiting results of select query

Note that end_row = start_row + num_rows - 1

• DB2

SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY

• Firebird

Versions > 2.0

SELECT columns FROM table ROWS start_row TO end_row

All versions

SELECT FIRST num_rows SKIP start_row columns FROM table

• Ingres

SELECT TOP num_rows columns FROM table
       or
SELECT FIRST num_rows columns FROM table

• MSSQL

Simple version (when start_row = 1)

SELECT TOP num_rows columns FROM table

Complex version (full-featured, requires ordering)

SELECT * FROM (
  SELECT TOP num_rows columns FROM (
    SELECT TOP num_rows + start_row columns FROM table ORDER BY some_key ASC
  ) AS newtable ORDER BY some_key DESC
) AS newtable2 ORDER BY some_key ASC

• MySQL

Versions > 4.0.14

SELECT columns FROM table LIMIT num_rows OFFSET start_row

All versions

SELECT columns FROM table LIMIT start_row, num_rows

• Oracle

Simple query

-- Notice: Will not work, if start_row > 1, since the first row will return false, and the cursor will terminate.
SELECT columns FROM table WHERE rownum >= start_row AND rownum <= end_row

This works fine without specifying start_row:

SELECT columns FROM table WHERE rownum <= end_row

This also works, but suffix num column in resultset:

SELECT * FROM (
  SELECT temp.*, rownum num FROM table ORDER BY columns
  ) WHERE num >= start_row and num <= end_row

• PostgreSQL, SQLite

SELECT columns FROM table LIMIT num_rows OFFSET start_row

• Virtuoso

SELECT columns FROM table LIMIT num_rows

• Linter

SELECT columns FROM table FETCH FIRST num_rows
SELECT columns FROM table LIMIT start_row, num_rows
(rows are numbered from 0)