SQL LIMIT & OFFSET

SQL
POSTGRES
INTERACTIVE

Complete SQL LIMIT & OFFSET Technical Guide

1. Fundamental Syntax 
-- Standard PostgreSQL/MySQL/SQLite syntax
SELECT * FROM table_name
ORDER BY column_name
LIMIT 10 OFFSET 20;

-- Alternative syntaxes
SELECT * FROM table_name
ORDER BY column_name
LIMIT 20, 10;  -- MySQL: OFFSET first, then LIMIT

-- SQL Server/Oracle syntax (different keywords)
SELECT * FROM table_name
ORDER BY column_name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
  1. LIMIT: Restricts the number of rows returned
  2. OFFSET: Skips a specified number of rows before returning results
  3. Database Variations: Different syntax across DBMS (TOP in SQL Server, ROWNUM in Oracle)
  4. Required: Typically used with ORDER BY for predictable results
2. Pagination Implementation
Basic Pagination
-- Page 1 (rows 1-10)
SELECT * FROM products
ORDER BY name
LIMIT 10 OFFSET 0;

-- Page 2 (rows 11-20)
SELECT * FROM products
ORDER BY name
LIMIT 10 OFFSET 10;

-- Dynamic pagination (application code)
SELECT * FROM products
ORDER BY name
LIMIT :page_size OFFSET :page_number * :page_size;
Keyset Pagination (Better Performance) 
-- First page
SELECT * FROM products
ORDER BY id, name
LIMIT 10;

-- Next page (using last seen values)
SELECT * FROM products
WHERE (id, name) > (last_seen_id, last_seen_name)
ORDER BY id, name
LIMIT 10;
3. Performance Optimization
OFFSET Efficiency
-- Problem: OFFSET scans all skipped rows
EXPLAIN ANALYZE SELECT * FROM large_table
ORDER BY id
LIMIT 10 OFFSET 1000000;

-- Solution: Keyset pagination avoids scanning
EXPLAIN ANALYZE SELECT * FROM large_table
WHERE id > last_seen_id
ORDER BY id
LIMIT 10;
Indexing Strategy
-- Essential for large datasets
CREATE INDEX idx_products_name ON products(name);

-- Covering index for optimal performance
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date)
INCLUDE (total_amount, status);
4. Database-Specific Syntax
SQL Server
-- Modern syntax (2012+)
SELECT * FROM products
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Legacy approach
SELECT TOP 10 * FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY name) AS rownum, *
  FROM products
) AS numbered
WHERE rownum > 20;
Oracle
-- Modern syntax (12c+)
SELECT * FROM products
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Traditional approach
SELECT * FROM (
  SELECT a.*, ROWNUM AS rnum FROM (
    SELECT * FROM products ORDER BY name
  ) a WHERE ROWNUM <= 30
) WHERE rnum > 20;
5. Advanced Usage Patterns
  1. Random Sampling: ORDER BY RANDOM() LIMIT n (performance intensive)
  2. Top-N Per Group: Window functions with LIMIT in subqueries
  3. Dynamic Limits: Stored procedures with parameterized LIMIT
  4. Materialized Views: Pre-compute paginated results for complex queries
Top-N Per Group Example
-- Get 3 most recent orders per customer
WITH ranked_orders AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
SELECT * FROM ranked_orders WHERE rn <= 3;