Editing Select (SQL) (section)

=== 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
|}