ORDER BY sorts the result rows, DISTINCT removes duplicate rows from the result, and LIMIT (or TOP/FETCH depending on the database) restricts how many rows are returned. These shape a result set for readability, sampling, and 'latest record' checks.
Relational tables have no inherent row order, so if order matters you must specify ORDER BY — never rely on insertion order. ORDER BY sorts ascending by default (ASC) or descending (DESC), and you can sort by multiple columns. DISTINCT collapses duplicate rows, useful when checking the set of values that appear in a column (e.g. which statuses exist). LIMIT is invaluable for testers: fetching the single most recent row for a user (ORDER BY created_at DESC LIMIT 1) is a common validation pattern, and limiting output keeps investigative queries manageable on large tables. Note the syntax varies: LIMIT (PostgreSQL/MySQL/SQLite), TOP (SQL Server), FETCH FIRST n ROWS ONLY (ANSI/Oracle) — worth knowing which your system uses.
To verify the most recent login event a user generated, a tester runs SELECT event, created_at FROM audit_log WHERE user_id = 7 ORDER BY created_at DESC LIMIT 1 — reliably fetching the latest row instead of hoping the default order returns it.
-- The single most recent order for a user
SELECT id, status, created_at
FROM orders
WHERE user_id = 7
ORDER BY created_at DESC
LIMIT 1;
-- Which distinct statuses exist in the orders table?
SELECT DISTINCT status FROM orders;
-- Multi-column sort: newest orders first, ties broken by id
SELECT id, user_id, created_at
FROM orders
ORDER BY created_at DESC, id DESC
LIMIT 20;Scenario: A tester writes SELECT id FROM orders WHERE user_id = 7 LIMIT 1 to grab a user's 'latest' order, and it works on their local database but returns an older order on the CI database. What is the flaw and how is it fixed?