Editing Select (SQL) (section)

== Limiting result rows ==
Often it is convenient to indicate a maximum number of rows that are returned. This can be used for testing or to prevent consuming excessive resources if the query returns more information than expected. The approach to do this often varies per vendor.

In [[International Organization for Standardization|ISO]] [[SQL:2003]], result sets may be limited by using
* [[Cursor (databases)|cursors]], or
* by adding a [[SQL window function]] to the SELECT-statement

ISO [[SQL:2008]] introduced the <code>FETCH FIRST</code> clause.

According to PostgreSQL v.9 documentation, an SQL window function "performs a calculation across a set of table rows that are somehow related to the current row", in a way similar to aggregate functions.<ref>[https://www.postgresql.org/docs/9.1/static/tutorial-window.html PostgreSQL 9.1.24 Documentation - Chapter 3. Advanced Features]</ref>
The name recalls signal processing [[window function |window functions]]. A window function call always contains an '''OVER''' clause.

=== ROW_NUMBER() window function ===
<code>ROW_NUMBER() OVER</code> may be used for a ''simple table'' on the returned rows, e.g. to return no more than ten rows:

<syntaxhighlight lang="sql" highlight="3">
SELECT * FROM
( SELECT
    ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS row_number,
    columns
  FROM tablename
) AS foo
WHERE row_number <= 10

</syntaxhighlight>

ROW_NUMBER can be [[Nondeterministic algorithm|non-deterministic]]: if ''sort_key'' is not unique, each time you run the query it is possible to get different row numbers assigned to any rows where ''sort_key'' is the same. When ''sort_key'' is unique, each row will always get a unique row number.

=== RANK() window function ===
The <code>RANK() OVER</code> window function acts like ROW_NUMBER, but may return more or less than ''n'' rows in case of tie conditions, e.g. to return the top-10 youngest persons:

<syntaxhighlight lang="sql" highlight="3">
SELECT * FROM (
  SELECT
    RANK() OVER (ORDER BY age ASC) AS ranking,
    person_id,
    person_name,
    age
  FROM person
) AS foo
WHERE ranking <= 10
</syntaxhighlight>

The above code could return more than ten rows, e.g. if there are two people of the same age, it could return eleven rows.

=== FETCH FIRST clause ===
Since ISO [[SQL:2008]] results limits can be specified as in the following example using the <code>FETCH FIRST</code> clause.

<syntaxhighlight lang="sql" highlight="2">SELECT * FROM T 
FETCH FIRST 10 ROWS ONLY</syntaxhighlight>

This clause currently is supported by CA DATACOM/DB 11, IBM DB2, SAP SQL Anywhere, PostgreSQL, EffiProz, H2, HSQLDB version 2.0, Oracle 12c and [[Mimer SQL]].

Microsoft SQL Server 2008 and higher [https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-2017#using-offset-and-fetch-to-limit-the-rows-returned supports <code>FETCH FIRST</code>], but it is considered part of the <code>ORDER BY</code> clause. The <code>ORDER BY</code>, <code>OFFSET</code>, and <code>FETCH FIRST</code> clauses are all required for this usage.

<syntaxhighlight lang="tsql" highlight="2">SELECT * FROM T 
ORDER BY acolumn DESC OFFSET 0 ROWS FETCH FIRST 10 ROWS ONLY</syntaxhighlight>

=== Non-standard syntax ===
Some DBMSs offer non-standard syntax either instead of or in addition to SQL standard syntax. Below, variants of the ''simple limit'' query for different DBMSes are listed:
{|class="wikitable"
|-
| <syntaxhighlight lang="tsql" highlight="1">SET ROWCOUNT 10
SELECT * FROM T</syntaxhighlight>
| [[Microsoft SQL Server|MS SQL Server]] (This also works on Microsoft SQL Server 6.5 while the '''Select top 10 * from T''' does not)
|-
| <syntaxhighlight lang="postgres" highlight="2">SELECT * FROM T 
LIMIT 10 OFFSET 20</syntaxhighlight>
| [[Netezza]], [[MySQL]], [[MariaDB]] (also supports the standard version, since version 10.6), [[SQL Anywhere|SAP SQL Anywhere]], [[PostgreSQL]] (also supports the standard, since version 8.4), [[SQLite]], [[HSQLDB]], [[H2 (DBMS)|H2]], [[Vertica]], [[Polyhedra DBMS|Polyhedra]], [[Couchbase Server]], [[Snowflake Computing]], [[Virtuoso Universal Server|OpenLink Virtuoso]]
|-
| <syntaxhighlight lang="sql" highlight="2">SELECT * from T 
WHERE ROWNUM <= 10</syntaxhighlight>
| [[Oracle database|Oracle]]
|-
| <code>SELECT '''FIRST 10''' * from T </code>
| [[Ingres (database)|Ingres]]
|-
| <code>SELECT '''FIRST 10''' * FROM T order by a </code>
| [[IBM Informix|Informix]]
|-
| <code>SELECT '''SKIP 20 FIRST 10''' * FROM T order by c, d </code>
| [[IBM Informix|Informix]] (row numbers are filtered after order by is evaluated.  SKIP clause was introduced in a v10.00.xC4 fixpack)
|-
| <code>SELECT '''TOP 10''' * FROM T</code>
| [[Microsoft SQL Server|MS SQL Server]], [[Adaptive Server Enterprise|SAP ASE]], [[Microsoft Access|MS Access]], [[SAP IQ]], [[Teradata]]
|-
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T 
SAMPLE 10</syntaxhighlight>
| [[Teradata]]
|-
| <code>SELECT '''TOP 20, 10''' * FROM T</code>
| [[Virtuoso Universal Server|OpenLink Virtuoso]] (skips 20, delivers next 10)<ref name="docs_9.19">{{Cite web |title=9.19.10. The TOP SELECT Option |author=OpenLink Software |work=docs.openlinksw.com |access-date=1 October 2019 |url= http://docs.openlinksw.com/virtuoso/topselectoption/ |language=en-US}}</ref>

|-
| <code>SELECT '''TOP 10 START AT 20''' * FROM T</code>
| [[SQL Anywhere|SAP SQL Anywhere]] (also supports the standard, since version 9.0.1)
|-
| <code>SELECT '''FIRST 10 SKIP 20''' * FROM T</code>
| [[Firebird (database server)|Firebird]]
|-
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T
ROWS 20 TO 30</syntaxhighlight>
| [[Firebird (database server)|Firebird]] (since version 2.1)
|-
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T
WHERE ID_T > 10 FETCH FIRST 10 ROWS ONLY</syntaxhighlight>
| [[IBM Db2]]
|-
| <syntaxhighlight lang="sql" highlight="2">SELECT * FROM T
WHERE ID_T > 20 FETCH FIRST 10 ROWS ONLY</syntaxhighlight>
| [[IBM Db2]] (new rows are filtered after comparing with key column of table T)
|}

=== Rows Pagination ===
'''Rows Pagination'''<ref>Ing. Óscar Bonilla, MBA</ref> is an approach used to limit and display only a part of the total data of a query in the database. Instead of showing hundreds or thousands of rows at the same time, the server is requested only one page (a limited set of rows, per example only 10 rows), and the user starts navigating by requesting the next page, and then the next one, and so on. It is very useful, specially in web systems, where there is no dedicated connection between the client and the server, so the client does not have to wait to read and display all the rows of the server.

==== Data in Pagination approach ====
* <code>{rows}</code> = Number of rows in a page
* <code>{page_number}</code> = Number of the current page
* <code>{begin_base_0}</code> = Number of the row - 1 where the page starts = (page_number-1) * rows

==== Simplest method (but very inefficient) ====
# Select all rows from the database
# Read all rows but send to display only when the row_number of the rows read is between <code>{begin_base_0 + 1}</code> and <code>{begin_base_0 + rows}</code>
<syntaxhighlight lang="sql">Select * 
from {table} 
order by {unique_key}</syntaxhighlight>

==== Other simple method (a little more efficient than read all rows) ====
# Select all the rows from the beginning of the table to the last row to display (<code>{begin_base_0 + rows}</code>)
# Read the <code>{begin_base_0 + rows}</code> rows but send to display only when the row_number of the rows read is greater than <code>{begin_base_0}</code>
{|class="wikitable"
|-
! SQL
! Dialect
|-
| 
<syntaxhighlight lang="postgresql">select *
from {table}
order by {unique_key}
FETCH FIRST {begin_base_0 + rows} ROWS ONLY</syntaxhighlight>
| SQL ANSI 2008<br>PostgreSQL<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12<br>Mimer SQL
|-
|
<syntaxhighlight lang="mysql">Select *
from {table}
order by {unique_key}
LIMIT {begin_base_0 + rows}</syntaxhighlight>
| MySQL<br>SQLite
|-
|
<syntaxhighlight lang="tsql">Select TOP {begin_base_0 + rows} * 
from {table} 
order by {unique_key}</syntaxhighlight>
| SQL Server 2005
|-
|
<syntaxhighlight lang="mysql">Select *
from {table}
order by {unique_key}
ROWS LIMIT {begin_base_0 + rows}</syntaxhighlight>
| Sybase, ASE 16 SP2
|-
|
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows}
Select * 
from {table} 
order by {unique_key}
SET ROWCOUNT 0</syntaxhighlight>
| Sybase, SQL Server 2000
|-
|
<syntaxhighlight lang="sql">Select *
    FROM (
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
where rownum <= {begin_base_0 + rows}</syntaxhighlight>
| Oracle 11
|}
<br>

==== Method with positioning ====
# Select only <code>{rows}</code> rows starting from the next row to display (<code>{begin_base_0 + 1}</code>)
# Read and send to display all the rows read from the database
{|class="wikitable"
|-
! SQL
! Dialect
|-
| 
<syntaxhighlight lang="postgres">Select *
from {table}
order by {unique_key}
OFFSET {begin_base_0} ROWS
FETCH NEXT {rows} ROWS ONLY</syntaxhighlight>
| SQL ANSI 2008<br>PostgreSQL<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12<br>Mimer SQL
|-
|
<syntaxhighlight lang="postgres">Select *
from {table}
order by {unique_key}
LIMIT {rows} OFFSET {begin_base_0}</syntaxhighlight>
| MySQL<br>MariaDB<br>PostgreSQL<br>SQLite
|-
|
<syntaxhighlight lang="mysql">Select * 
from {table} 
order by {unique_key}
LIMIT {begin_base_0}, {rows}</syntaxhighlight>
| MySQL<br>MariaDB<br>SQLite
|-
|
<syntaxhighlight lang="mysql">Select *
from {table}
order by {unique_key}
ROWS LIMIT {rows} OFFSET {begin_base_0}</syntaxhighlight>
| Sybase, ASE 16 SP2
|-
|
<syntaxhighlight lang="tsql">Select TOP {begin_base_0 + rows}
       *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp</syntaxhighlight>
| Sybase 12.5.3:
|-
|
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(10) 
into #temp
from {table}
ORDER BY {unique_key} 
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0</syntaxhighlight>
| Sybase 12.5.2:
|-
|
<syntaxhighlight lang="tsql">select TOP {rows} * 
from (
      select *, ROW_NUMBER() over (order by {unique_key}) as _offset
      from {table}
) xx 
where _offset > {begin_base_0}</syntaxhighlight>
<br>
| SQL Server 2005
|-
|
<syntaxhighlight lang="tsql">SET ROWCOUNT {begin_base_0 + rows}
select *,  _offset=identity(int,1,1) 
into #temp
from {table}
ORDER BY {unique-key}
select * from #temp where _offset > {begin_base_0}
DROP TABLE #temp
SET ROWCOUNT 0</syntaxhighlight>
| SQL Server 2000
|-
|
<syntaxhighlight lang="sql">SELECT * FROM (
    SELECT rownum-1 as _offset, a.* 
    FROM(
        SELECT * 
        FROM {table} 
        ORDER BY {unique_key}
    ) a 
    WHERE rownum <= {begin_base_0 + cant_regs}
)
WHERE _offset >= {begin_base_0}</syntaxhighlight>
| Oracle 11
|}
<br>

==== Method with filter (it is more sophisticated but necessary for very big dataset) ====
# Select only then <code>{rows}</code> rows with filter:
## First Page: select only the first <code>{rows}</code> rows, depending on the type of database
## Next Page: select only the first <code>{rows}</code> rows, depending on the type of database, where the <code>{unique_key}</code> is greater than <code>{last_val}</code> (the value of the <code>{unique_key}</code> of the last row in the current page)
## Previous Page: sort the data in the reverse order, select only the first <code>{rows}</code> rows, where the <code>{unique_key}</code> is less than <code>{first_val}</code> (the value of the <code>{unique_key}</code> of the first row in the current page), and sort the result in the correct order
# Read and send to display all the rows read from the database

{|class="wikitable"
|-
! First Page
! Next Page
! Previous Page
! Dialect
|-
| 
<syntaxhighlight lang="postgresql">select *
from {table} 
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY</syntaxhighlight>
| 
<syntaxhighlight lang="postgresql">select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
FETCH FIRST {rows} ROWS ONLY</syntaxhighlight>
| 
<syntaxhighlight lang="postgresql">select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   FETCH FIRST {rows} ROWS ONLY
 ) a
 order by {unique_key}</syntaxhighlight>
| SQL ANSI 2008<br>PostgreSQL<br>SQL Server 2012<br>Derby<br>Oracle 12c<br>DB2 12<br>Mimer SQL
|-
|
<syntaxhighlight lang="mysql">select *
from {table}
order by {unique_key}
LIMIT {rows}</syntaxhighlight>
| 
<syntaxhighlight lang="mysql">select * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
LIMIT {rows}</syntaxhighlight>
| 
<syntaxhighlight lang="mysql">select * 
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
   LIMIT {rows}
 ) a
 order by {unique_key}</syntaxhighlight>
| MySQL<br>SQLite
|-
| 
<syntaxhighlight lang="tsql">select TOP {rows} * 
from {table} 
order by {unique_key}</syntaxhighlight>
| 
<syntaxhighlight lang="tsql">select TOP {rows} * 
from {table} 
where {unique_key} > {last_val}
order by {unique_key}</syntaxhighlight>
| 
<syntaxhighlight lang="tsql">select * 
 from (
   select TOP {rows} * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}</syntaxhighlight>
| SQL Server 2005
|-
| 
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows}
select *
from {table} 
order by {unique_key}
SET ROWCOUNT 0</syntaxhighlight>
| 
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows}
select *
from {table} 
where {unique_key} > {last_val}
order by {unique_key}
SET ROWCOUNT 0</syntaxhighlight>
|
<syntaxhighlight lang="tsql">SET ROWCOUNT {rows}
 select *
 from (
   select * 
   from {table} 
   where {unique_key} < {first_val}
   order by {unique_key} DESC
 ) a
 order by {unique_key}
 SET ROWCOUNT 0</syntaxhighlight>
| Sybase, SQL Server 2000
|-
| 
<syntaxhighlight lang="sql">select *
from (
    select * 
    from {table} 
    order by {unique_key}
  ) a 
where rownum <= {rows}</syntaxhighlight>
| 
<syntaxhighlight lang="sql">select *
from (
  select * 
  from {table} 
  where {unique_key} > {last_val}
  order by {unique_key}
) a 
where rownum <= {rows}</syntaxhighlight>
|
<syntaxhighlight lang="sql">select * 
 from (
   select *
   from (
     select * 
     from {table} 
     where {unique_key} < {first_val}
     order by {unique_key} DESC
   ) a1
   where rownum <= {rows}
 ) a2
 order by {unique_key}</syntaxhighlight>
| Oracle 11
|}