#PostgreSQL LIMIT Clause

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column]
LIMIT number;

-- Return first 10 records
SELECT * FROM users LIMIT 10;

-- Return first 5 products
SELECT name, price FROM products LIMIT 5;

-- Get top 5 most expensive products
SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Get 10 most recent orders
SELECT id, created_at, total_amount
FROM orders
ORDER BY created_at DESC
LIMIT 10;

SELECT column1, column2, ...
FROM table_name
ORDER BY column
LIMIT number OFFSET skip;

-- Page 1 (10 per page)
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 0;

-- Page 2
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 10;

-- Page 3
SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;

-- General formula: OFFSET = (page - 1) * page_size

-- Equivalent to LIMIT 10
SELECT * FROM products
ORDER BY price DESC
FETCH FIRST 10 ROWS ONLY;

-- Equivalent to LIMIT 10 OFFSET 5
SELECT * FROM products
ORDER BY price DESC
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

-- These are all equivalent
FETCH FIRST 10 ROWS ONLY
FETCH NEXT 10 ROWS ONLY
FETCH FIRST 10 ROW ONLY
FETCH NEXT 10 ROW ONLY

-- Top 10 products by sales
SELECT p.name, SUM(oi.quantity * oi.unit_price) as total_sales
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.id, p.name
ORDER BY total_sales DESC
LIMIT 10;

-- Top 5 most active users
SELECT u.username, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username
ORDER BY order_count DESC
LIMIT 5;

-- Assuming 20 per page, get specified page
-- page: page number (starting from 1)
-- page_size: items per page

SELECT id, name, email, created_at
FROM users
ORDER BY created_at DESC
LIMIT 20 OFFSET ((3 - 1) * 20);  -- Page 3

-- Get total count for pagination info
SELECT COUNT(*) FROM users;

-- Get top 3 most expensive products in each category
SELECT * FROM (
    SELECT
        p.*,
        ROW_NUMBER() OVER (
            PARTITION BY category_id
            ORDER BY price DESC
        ) as rn
    FROM products p
) ranked
WHERE rn <= 3;

-- These two queries are equivalent
SELECT * FROM products LIMIT ALL;
SELECT * FROM products;

-- Large OFFSET values have poor performance
SELECT * FROM logs ORDER BY id LIMIT 10 OFFSET 1000000;
-- Must scan first 1,000,010 rows

-- Optimization: Use cursor or keyset pagination
SELECT * FROM logs
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- Not recommended: Results are indeterminate without ORDER BY
SELECT * FROM users LIMIT 10;

-- Recommended: Explicit ordering
SELECT * FROM users ORDER BY id LIMIT 10;

-- NULLs are last by default (ASC) or first (DESC)
SELECT * FROM products ORDER BY price NULLS FIRST LIMIT 10;
SELECT * FROM products ORDER BY price DESC NULLS LAST LIMIT 10;

SELECT column_list
FROM table_name
WHERE condition
GROUP BY grouping_columns
HAVING group_condition
ORDER BY sorting_columns
LIMIT number
OFFSET skip;

-- Traditional OFFSET pagination (slow with large data)
SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 10000;

-- Keyset pagination (faster)
SELECT * FROM products
WHERE id > 10020  -- Last ID from previous page
ORDER BY id
LIMIT 20;