SELECT * FROM my_table LIMIT x, y(Mysql)
or this:
SELECT * FROM my_table LIMIT x OFFSET y(PostgreSQL)
in a DBMS that doesn't support LIMIT statements (take Oracle for example).
In Oracle, you can use ROWNUM, which holds the current row number:
SELECT * FROMIn SQL Server (>2005), you can use a similar trick, using the ROW_NUMBER function:
(SELECT ROWNUM limit, * FROM my_table ORDER BY order_field)
WHERE limit BETWEEN x AND y
WITH ordered AS (When using another database (Access for example), you can use TOP when available:
SELECT ROW_NUMBER() OVER (ORDER BY order_field) AS limit, *
FROM my_table)
SELECT *
FROM ordered
WHERE limit BETWEEN x AND y
SELECT * FROMOr, another option:
(SELECT TOP x * FROM
(SELECT TOP x+y * FROM my_table ORDER BY order_field)
AS innerT
ORDER BY order_field DESC) AS outerT
ORDER BY order_field
SELECT TOP x * FROM my_tableSome databases don't allow TOP in subselects, so we have to use yet another method:
WHERE order_field NOT IN
(SELECT TOP y * FROM my_table ORDER BY order_field)
SELECT * FROM my_table outerTShould that fail too, you can always do:
WHERE
(SELECT COUNT(*)
FROM my_table innerT
WHERE innerT.order_field <= outerT.order_field)
BETWEEN y+1 AND x+y
ORDER BY outerT.order_field ASC;
SELECT TOP x * FROM my_table WHERE order_field > zwith z the last fetched order_field value from the previous page.
You can also find a handy article on the IBM site describing how you can simulate row numbers.
