← Data Analytics from Scratch: SQL, Spreadsheets and Metrics
Lesson
Filtering, sorting, DISTINCT and LIMIT
Learner can refine a query with WHERE, ORDER BY, DISTINCT and LIMIT and can predict the exact rows returned on a small table.
Refining queries: WHERE, ORDER BY, DISTINCT, LIMIT
Refining queries: WHERE, ORDER BY, DISTINCT, LIMIT
By default SELECT returns every row. Four clauses let you narrow and shape the result set.
WHERE filters rows by a condition before they are returned. Conditions use operators like = (equals), > (greater than), < (less than), and you can combine them with AND or OR. Example: SELECT * FROM orders WHERE city = 'London' returns only the two London rows (order_id 1 and 3, amounts 50 and 70).
ORDER BY sorts the result. Adding ASC gives ascending order (default), DESC gives descending. Example: SELECT * FROM orders ORDER BY amount DESC returns all five rows sorted from the largest amount (70) to the smallest (20).
DISTINCT removes duplicate values. SELECT DISTINCT city FROM orders returns just two rows: London and Paris, not five.
LIMIT caps how many rows come back. SELECT * FROM orders LIMIT 3 returns only the first three rows (order_ids 1, 2, 3). These clauses can be combined: SELECT city, amount FROM orders WHERE amount > 30 ORDER BY amount DESC LIMIT 2 first keeps rows with amount above 30 (amounts 50, 40, 70), then sorts them descending (70, 50, 40), then returns only the top 2: city London/amount 70 and city London/amount 50.
Lesson notes
Refining queries: WHERE, ORDER BY, DISTINCT, LIMIT
By default SELECT returns every row. Four clauses let you narrow and shape the result set.
WHERE filters rows by a condition before they are returned. Conditions use operators like = (equals), > (greater than), < (less than), and you can combine them with AND or OR. Example: SELECT * FROM orders WHERE city = 'London' returns only the two London rows (order_id 1 and 3, amounts 50 and 70).
ORDER BY sorts the result. Adding ASC gives ascending order (default), DESC gives descending. Example: SELECT * FROM orders ORDER BY amount DESC returns all five rows sorted from the largest amount (70) to the smallest (20).
DISTINCT removes duplicate values. SELECT DISTINCT city FROM orders returns just two rows: London and Paris, not five.
LIMIT caps how many rows come back. SELECT * FROM orders LIMIT 3 returns only the first three rows (order_ids 1, 2, 3). These clauses can be combined: SELECT city, amount FROM orders WHERE amount > 30 ORDER BY amount DESC LIMIT 2 first keeps rows with amount above 30 (amounts 50, 40, 70), then sorts them descending (70, 50, 40), then returns only the top 2: city London/amount 70 and city London/amount 50.