Paginated Query

The statements to retrieve partial rows from selected data are like followings according to the DBMS.

Oracle (8i or higer)

Use ROW_NUMBER() function and subquery. In some cases, such as hierarchical query (START WITH ~ CONNECT BY ~ clause) using ORDER SIBLINGS BY, ROW_NUMBER() function can't be used in subquery, so in those cases, ROWNUM with double-nested subquery can be used.

• Syntax :

SELECT *
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY ~) AS row_no, ~
   FROM ~ 
   WHERE ~ )
WHERE row_no BETWEEN m AND n

• Example :

select *
from(
  select row_number(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
  from tb_product
  where category_id = 101)
where row_no between 21 and 30

Microsoft SQL Server (2005 or higher)

• Syntax :

WITH ~ AS(
   SELECT ~ , ROW_NUMBER() OVER(~) AS row_no
   FROM ~
   WHERE ~
)
SELECT ~
FROM ~
WHERE row_no BETWEEN m AND n

• Example :

with v_product as (
   select row_number() over(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
   from tb_product
   where category_id = 101)
select *
from v_product
where row_no between 21 and 30

MySQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~
LIMIT offset, row_count

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 20, 10

The query with both LIMIT clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

PostgresSQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~ 
LIMIT row_count OFFSET offset

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 10 offset 20

The query with both LIMIT/OFFSET clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

Informix

• Syntax :

SELECT SKIP offset FIRST row_count selection_list
FORM ~
WHERE ~
ORDER BY ~

Readings

• Limiting result rows of SELECT (from Wikipedia)