Jump to content

SQL Dialects Reference/Select queries/Limiting number of rows returned

Add links
From Wikibooks, open books for an open world

Limiting number of rows returned

[edit | edit source]

Note that end_row = start_row + num_rows - 1

Standard SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY

SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY

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 FIRST num_rows columns FROM table
  • SELECT columns FROM table OFFSET start_row FETCH {FIRST|NEXT} num_rows ROWS ONLY
Linter 
SELECT columns FROM table FETCH FIRST num_rows
SELECT columns FROM table LIMIT start_row, num_rows
(rows are numbered from 0)
MonetDB SELECT columns FROM table LIMIT num_rows OFFSET start_row
MSSQL
  • SELECT columns FROM table OFFSET start_row ROWS FETCH { FIRST | NEXT } num_rows ROW[S] ONLY
  • Simple version (when start_row = 0):
SELECT TOP num_rows columns FROM table
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

Since 12.1, similar to standards: 

SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY
SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY

Full syntax: (Can have OFFSET, or FETCH, or both clauses) 

SELECT column [, column2 …] FROM table [ORDER BY column2] 
[OFFSET start_row ROWS] 
[FETCH [FIRST|NEXT] [num_rows|percent PERCENT] ROWS [ONLY|WITH TIES]]
PostgreSQL SELECT columns FROM table LIMIT num_rows OFFSET start_row

or: The SQL standard syntax.

SQLite SELECT columns FROM table LIMIT num_rows OFFSET start_row
Virtuoso 
SELECT TOP num_rows columns FROM table
SELECT TOP skip_rows,num_rows columns FROM table


Standard -
DB2 ?
Firebird ?
Ingres ?
Linter ?
MonetDB ?
MSSQL NOT IN()
MySQL NOT IN()
Oracle MINUS
PostgreSQL ?
SQLite ?
Virtuoso ?
Retrieved from "https://en.wikibooks.org/w/index.php?title=SQL_Dialects_Reference/Select_queries/Limiting_number_of_rows_returned&oldid=4431271"