Simulating LIMIT with offsets in SQL

How can you do this (x is number of rows to fetch, y is offset) (Mysql):
SELECT * FROM my_table LIMIT x, y or this (PostgreSQL) :

SELECT * FROM my_table LIMIT x OFFSET y

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 * FROM
(SELECT ROWNUM limit, * FROM my_table ORDER BY order_field)
WHERE limit BETWEEN x AND y In SQL Server (>2005), you can use a similar trick, using the ROW_NUMBER function:
WITH ordered AS (
SELECT ROW_NUMBER() OVER (ORDER BY order_field) AS limit,
FROM my_table)
SELECT

FROM ordered
WHERE limit BETWEEN x AND y

When using another database (Access for example), you can use TOP when available:
SELECT * FROM
(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

Or, another option:

SELECT TOP x * FROM my_table  
WHERE order_field NOT IN  
(SELECT TOP y * FROM my_table ORDER BY order_field)

Some databases don’t allow TOP in subselects, so we have to use yet another method:
SELECT * FROM my_table outerT
WHERE
(SELECT COUNT(*)
FROM my_table innerT
WHERE innerT.order_field order_field)
BETWEEN y+1 AND x+y
ORDER BY outerT.order_field ASC; Should that fail too, you can always do:

SELECT TOP x * FROM my_table WHERE order_field > z

With 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.