PostgreSQL LIMIT

Key Concepts

LIMIT 10

LIMIT 10 OFFSET 20

LIMIT 25 OFFSET 50

ORDER BY price DESC LIMIT 5

🔹 Basic LIMIT

The LIMIT clause restricts the number of rows returned by your query. It's placed at the end of the SELECT statement and is particularly useful for large tables where you only need a sample or preview of the data, improving performance significantly.

-- Get first 5 customers
SELECT * FROM customers LIMIT 5;

-- Get 10 most recent orders
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10;

-- Get 3 cheapest products
SELECT product_name, price 
FROM products 
ORDER BY price ASC 
LIMIT 3;

🔹 LIMIT with OFFSET

OFFSET skips a specified number of rows before returning results. Combined with LIMIT, it enables pagination by dividing large result sets into manageable pages. This is essential for web applications displaying data across multiple pages with next and previous buttons.

-- Skip first 5 rows, get next 5
SELECT * FROM customers 
LIMIT 5 OFFSET 5;

-- Page 1: First 10 items
SELECT * FROM products 
ORDER BY product_id 
LIMIT 10 OFFSET 0;

-- Page 2: Next 10 items
SELECT * FROM products 
ORDER BY product_id 
LIMIT 10 OFFSET 10;

-- Page 3: Next 10 items
SELECT * FROM products 
ORDER BY product_id 
LIMIT 10 OFFSET 20;

🔹 Pagination Formula

To implement pagination, calculate OFFSET using the page number and page size. The formula is: OFFSET = (page_number - 1) × page_size. This ensures each page shows the correct set of results without overlap, creating a smooth browsing experience for users.

-- Formula: OFFSET = (page_number - 1) * page_size

-- Page 1 (page_size = 20)
SELECT * FROM products 
LIMIT 20 OFFSET 0;  -- (1-1) * 20 = 0

-- Page 2
SELECT * FROM products 
LIMIT 20 OFFSET 20;  -- (2-1) * 20 = 20

-- Page 3
SELECT * FROM products 
LIMIT 20 OFFSET 40;  -- (3-1) * 20 = 40

-- Page 5 with 25 items per page
SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 25 OFFSET 100;  -- (5-1) * 25 = 100

🔹 Top N Results

Combine LIMIT with ORDER BY to get top or bottom N results based on any criteria. This is perfect for finding best sellers, highest prices, most recent entries, or any ranked data. Always use ORDER BY with LIMIT for predictable, meaningful results.

-- Top 5 most expensive products
SELECT product_name, price 
FROM products 
ORDER BY price DESC 
LIMIT 5;

-- Top 10 customers by total purchases
SELECT customer_id, SUM(total) AS total_spent 
FROM orders 
GROUP BY customer_id 
ORDER BY total_spent DESC 
LIMIT 10;

-- 5 newest users
SELECT name, created_date 
FROM users 
ORDER BY created_date DESC 
LIMIT 5;

-- Bottom 3 products by stock
SELECT product_name, stock 
FROM products 
ORDER BY stock ASC 
LIMIT 3;

🔹 LIMIT with WHERE and ORDER BY

You can combine LIMIT with WHERE for filtering and ORDER BY for sorting to create powerful, targeted queries. The execution order is: WHERE filters first, ORDER BY sorts the filtered results, then LIMIT restricts the final output. This combination is extremely common in real applications.

-- Top 5 electronics under $500
SELECT product_name, price 
FROM products 
WHERE category = 'Electronics' AND price < 500 
ORDER BY price DESC 
LIMIT 5;

-- 10 most recent orders from USA
SELECT order_id, customer_name, order_date 
FROM orders 
WHERE country = 'USA' 
ORDER BY order_date DESC 
LIMIT 10;

-- First 3 active users sorted by name
SELECT name, email, status 
FROM users 
WHERE status = 'active' 
ORDER BY name 
LIMIT 3;

🔹 Performance Tips

Using LIMIT improves query performance by reducing the amount of data processed and transferred. Always combine LIMIT with appropriate indexes on columns used in WHERE and ORDER BY clauses. For large offsets, consider using cursor-based pagination instead of OFFSET for better performance.