SQL Dialects Reference/Select queries/Limiting results of select query
From Wikibooks, open books for an open world
This page may need to be [edit] Limiting results of select query
Note that end_row = start_row + num_rows - 1
| Standard | ? |
|---|---|
| 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 | |
| Linter |
SELECT columns FROM table FETCH FIRST num_rows SELECT columns FROM table LIMIT start_row, num_rows (rows are numbered from 0) |
| 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 | SELECT columns FROM table LIMIT num_rows OFFSET start_row |
| 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 |
