Editing Select (SQL) (section)

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