SQL SELECT TOP

-- SQL Server / MS Access
SELECT TOP 10 id, name
FROM users
ORDER BY signup_date DESC;

-- Or as a percentage
SELECT TOP 5 PERCENT id, name
FROM users
ORDER BY total_spent DESC;

-- Postgres / SQLite / MySQL
SELECT id, name
FROM users
ORDER BY signup_date DESC
LIMIT 10;

-- Offset (skip the first N) - paging
SELECT id, name
FROM users
ORDER BY signup_date DESC
LIMIT 10 OFFSET 20;       -- page 3 of 10-per-page

-- Cursor-based paging (recommended for large datasets)
-- First page
SELECT id, name, signup_date
FROM users
WHERE signup_date < NOW()
ORDER BY signup_date DESC, id DESC
LIMIT 10;

-- Next page - pass the LAST row's values as a 'keyset' filter
SELECT id, name, signup_date
FROM users
WHERE (signup_date, id) < ('2026-01-15 10:00:00', 12345)
ORDER BY signup_date DESC, id DESC
LIMIT 10;

-- This scales much better than OFFSET because OFFSET still scans the skipped rows.

-- SQL Server modern alternative - OFFSET FETCH
SELECT id, name
FROM users
ORDER BY signup_date DESC
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- Common mistake: LIMIT without ORDER BY
-- Without an explicit order, the database can return rows in ANY order.
-- Always pair LIMIT with ORDER BY in production code.

-- Top-N per group (window function)
SELECT *
FROM (
  SELECT
    o.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
  FROM orders o
) ranked
WHERE rn <= 3;          -- top 3 orders per user

-- SQL Server
SELECT TOP 5 * FROM products;
-- MySQL / PostgreSQL
SELECT * FROM products LIMIT 5;