SELECT: Choosing Columns, Sorting & Limiting
The shape of every query: SELECT columns FROM a table. Pick and rename columns, calculate new ones, remove duplicates with DISTINCT, sort with ORDER BY, and take the top rows with LIMIT.
The shape of a query
Every query that reads data starts with two parts: SELECT (which columns) and FROM (which table). A semicolon ends the statement.
Use * for every column: SELECT * FROM products;. It is handy for a first look, but in real queries list the columns you need: it is clearer and faster.
SQL keywords are not case-sensitive (select works too), but writing them in capitals is the usual style. Text values go in single quotes: 'Brisbane'. Comments start with --.
Calculated columns and aliases
A column in SELECT can be a calculation. Give it a readable name with AS (an alias):
Arithmetic uses + - * /. In SQLite and PostgreSQL, dividing two whole numbers gives a whole number (7 / 2 is 3), so write 7 / 2.0 or 7 * 1.0 / 2 to get 3.5.
DISTINCT: unique values
SELECT DISTINCT removes duplicate rows from the result. SELECT DISTINCT category FROM products; lists each category once. With several columns, it removes rows where all the listed columns are the same.
ORDER BY: sorting
ORDER BY sorts the result. ASC (smallest first) is the default; DESC sorts largest first. Sort by several columns: the second only breaks ties in the first.
Without ORDER BY, the database may return rows in any order, and the order can change. If order matters, always say so.
LIMIT and OFFSET: the top few rows
LIMIT n returns only the first n rows: with ORDER BY, that is a "top n" query. OFFSET skips rows first, which is how websites show page 2, page 3 and so on.
SQL Server writes this as SELECT TOP 3 …, and the standard form is FETCH FIRST 3 ROWS ONLY.
Try it: select, sort and limit
Run each preset, then change it: different columns, a different sort, a different limit.
Interactive: this part runs in your browser.
The order you write vs the order it runs
You write SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT, but the database works in a different order: first FROM (get the table), then WHERE (filter rows), GROUP BY, HAVING, then SELECT (compute columns), then ORDER BY and LIMIT. This explains a classic error: an alias made in SELECT cannot be used in WHERE, because WHERE runs first. It can be used in ORDER BY, which runs after.
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show the name and price of every product, most expensive first.
Show the name and price of the 3 cheapest products, cheapest first.
Show each product's name, price, and a column called price_gst: the price plus 10%, rounded to 2 decimals.
List each product category once, in alphabetical order.
Summary
Key words
- query
- a request for data, starting with SELECT
- clause
- a part of a query: SELECT, FROM, WHERE, ORDER BY…
- alias
- a new name for a column or table, with AS
- DISTINCT
- removes duplicate rows
- ORDER BY … DESC
- sorts, largest first
- LIMIT / OFFSET
- take the first n rows / skip some rows first
Common mistakes
- Double quotes for text:
"Brisbane"means a column called Brisbane in standard SQL. Use single quotes:'Brisbane'. - A trailing comma before FROM:
SELECT name, price, FROM productsis a syntax error. - Relying on the order rows come back in without ORDER BY.
- Expecting
7 / 2to be 3.5: use7 / 2.0. - Using a SELECT alias in WHERE.
Quiz
1. Which query lists each category only once?
2. How do you get the 5 cheapest products?
3. Which quotes are for text values in SQL?
4. What does SELECT 7 / 2 return in SQLite?
5. Without ORDER BY, the order of rows is…
📚 Further reading
Trusted, mostly free sources: official documentation, well-known textbooks and courses. When you want to confirm or go deeper on anything here, start with these.
- SQLBolt ↗ free interactive SQL lessons in the browser
- Select Star SQL ↗ a free interactive book on SQL