ROWNUMBER Pagination Pattern

ROWNUMBER Pagination Pattern is a SQL statement in the SELECT Queries category. Generic pagination pattern using ROW_NUMBER(). Works across all databases that support window functions. The syntax is SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY col) AS rn FROM table) sub WHERE rn BETWEEN @start AND @end;. It returns paginated results. A typical example: SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS row_num FROM employees ) AS paginated WHERE row_num BETWEEN 11 AND 20; -- Page 2 of employees (rows 11-20) -- PostgreSQL/MySQL 8+ / SQL Server / Oracle: -- All support this pattern -- Modern alternative with OFFSET/FETCH or LIMIT/OFFSET: -- SELECT * FROM employees ORDER BY id OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY; -- SELECT *… A close relative is SELECT, which retrieves data from a table. The most fundamental SQL statement. A close relative is SELECT *, which selects all columns from a table.