#PostgreSQL LIMIT and OFFSET

SELECT column1, column2, ...
FROM table_name
LIMIT number;

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

-- Get top 5 highest-paid employees
SELECT employee_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

-- Get a sample of 100 records for testing
SELECT * FROM large_table
LIMIT 100;

-- Get random sample
SELECT * FROM products
ORDER BY RANDOM()
LIMIT 20;

SELECT column1, column2, ...
FROM table_name
LIMIT number OFFSET number;

-- Skip first 10 rows, return next 10
SELECT * FROM customers
LIMIT 10 OFFSET 10;

-- Get page 3 (rows 21-30)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Page 1 (rows 1-10)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 0;

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

-- Page 3 (rows 21-30)
SELECT * FROM products
ORDER BY product_id
LIMIT 10 OFFSET 20;

-- Calculate offset based on page number
-- page_size = 20, page_number = 3
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET (3 - 1) * 20;  -- OFFSET 40

-- Get paginated results with total count
WITH paginated_data AS (
    SELECT *,
           COUNT(*) OVER() as total_count
    FROM products
    WHERE category = 'Electronics'
    ORDER BY price DESC
)
SELECT *
FROM paginated_data
LIMIT 10 OFFSET 0;

-- Get top 10 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 10;

-- Get 5 most recent orders
SELECT order_id, order_date, total_amount
FROM orders
ORDER BY order_date DESC
LIMIT 5;

-- Get oldest 10 customers
SELECT customer_name, registration_date
FROM customers
ORDER BY registration_date ASC
LIMIT 10;

-- Get top 3 products per category
SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
    FROM products
) ranked
WHERE rn <= 3;

-- Get customers who placed the most orders (top 10)
SELECT c.customer_id, c.customer_name, COUNT(*) as order_count
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY order_count DESC
LIMIT 10;

-- Get 20 unique product categories
SELECT DISTINCT category
FROM products
ORDER BY category
LIMIT 20;

-- Returns all rows
SELECT * FROM customers
LIMIT ALL;

-- Useful in dynamic queries where limit might be optional
SELECT * FROM customers
LIMIT CASE WHEN @apply_limit THEN 10 ELSE NULL END;

-- Create index for better performance
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- Query will be faster
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 10;

-- Slow: Large offset scans many rows
SELECT * FROM large_table
ORDER BY id
LIMIT 10 OFFSET 1000000;

-- Better: Use keyset pagination
SELECT * FROM large_table
WHERE id > 1000000
ORDER BY id
LIMIT 10;

-- First page
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

-- Next page (using last order_id from previous page)
SELECT * FROM orders
WHERE order_date >= '2024-01-01'
  AND (order_date, order_id) < ('2024-01-15', 12345)
ORDER BY order_date DESC, order_id DESC
LIMIT 10;

-- Top 10 customers by revenue
SELECT customer_id,
       customer_name,
       SUM(order_amount) as total_revenue
FROM orders
JOIN customers USING (customer_id)
GROUP BY customer_id, customer_name
ORDER BY total_revenue DESC
LIMIT 10;

-- Get 50 most recent log entries
SELECT * FROM application_logs
ORDER BY created_at DESC
LIMIT 50;

-- Get random 100 products for A/B testing
SELECT * FROM products
WHERE status = 'active'
ORDER BY RANDOM()
LIMIT 100;

-- Get top 5 customers with their latest order
SELECT DISTINCT ON (c.customer_id)
       c.customer_id,
       c.customer_name,
       o.order_id,
       o.order_date,
       o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id, o.order_date DESC
LIMIT 5;

-- Get products that are in top 10 by sales
SELECT *
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    ORDER BY SUM(quantity) DESC
    LIMIT 10
);

-- Dashboard: Top 5 selling products this month
SELECT p.product_name,
       SUM(oi.quantity) as units_sold,
       SUM(oi.quantity * oi.price) as revenue
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY p.product_id, p.product_name
ORDER BY revenue DESC
LIMIT 5;

-- User leaderboard: Top 20 by points
SELECT user_id,
       username,
       total_points,
       RANK() OVER (ORDER BY total_points DESC) as rank
FROM users
WHERE status = 'active'
ORDER BY total_points DESC
LIMIT 20;

-- Preview first 10 rows of imported data
SELECT * FROM imported_data
ORDER BY import_timestamp DESC
LIMIT 10;

-- LIMIT syntax
SELECT * FROM customers
LIMIT 10 OFFSET 5;

-- FETCH syntax (SQL standard)
SELECT * FROM customers
OFFSET 5 ROWS
FETCH FIRST 10 ROWS ONLY;

-- FETCH NEXT is equivalent to FETCH FIRST
SELECT * FROM customers
OFFSET 5 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Bad: Results are unpredictable
SELECT * FROM products
LIMIT 10;

-- Good: Consistent results
SELECT * FROM products
ORDER BY product_id
LIMIT 10;

-- Inefficient
SELECT * FROM orders
WHERE customer_id = 123
LIMIT 1;

-- Better: Use EXISTS
SELECT EXISTS (
    SELECT 1 FROM orders
    WHERE customer_id = 123
);

-- Slow for large offsets
SELECT * FROM orders
ORDER BY order_date
LIMIT 10 OFFSET 100000;

-- Better: Use WHERE clause
SELECT * FROM orders
WHERE order_date < '2023-01-01'
ORDER BY order_date DESC
LIMIT 10;