You found 500 orders. Now what? You probably want the most recent ones first. Or the highest value ones. Or just the first 10 results. ORDER BY sorts. LIMIT caps. OFFSET skips. Together they give you pagination — exactly what the UI does when it shows "Page 1 of 50".
-- Sort by newest first
SELECT * FROM orders ORDER BY created_at DESC;
-- Sort by oldest first
SELECT * FROM orders ORDER BY created_at ASC;
-- Sort by multiple columns
SELECT * FROM users ORDER BY status ASC, created_at DESC;
-- Get only the 10 most recent orders
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;
-- Pagination: Page 2 (skip first 10, get next 10)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10;
-- Find the single most expensive order
SELECT * FROM orders ORDER BY total_amount DESC LIMIT 1;
-- QA scenario: Verify the latest order belongs to the right user
SELECT order_id, user_id, total_amount, status, created_at
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 1;When testing pagination in the UI, run the same query with LIMIT and OFFSET in SQL. If the UI shows different data than your SQL query, you have found a bug — possibly a missing ORDER BY in the API, or an off-by-one error in OFFSET.
Key Point: ORDER BY sorts results. LIMIT caps row count. OFFSET skips rows. Together they power pagination.