Your first SQL queries: SELECT, WHERE, ORDER BY, LIMIT and GROUP BY

A SQL query describes what data you want, not how to fetch it. Almost every query you write starts with the same few clauses. The examples use two small tables that appear throughout the SQL articles on Commitpedia:

customers                         orders
id | name  | city   | email       id | customer_id | amount | status
1  | Ada   | London | ada@...     10 | 1           | 25.00  | paid
2  | Brian | Berlin | NULL        11 | 1           | 140.00 | paid
3  | Chen  | London | chen@...    12 | 2           | 60.00  | refunded
4  | Dana  | NULL   | dana@...    13 | 3           | 15.50  | paid
                                  14 | 3           | 99.90  | pending

Every query here runs unchanged in SQLite, PostgreSQL and MySQL. To follow along without installing a server, open a terminal and type sqlite3 shop.db.

SELECT: choose columns

SELECT name, city FROM customers;   -- two columns, every row
SELECT * FROM customers;            -- all columns (handy while exploring)
SELECT name AS customer FROM customers;  -- rename a column in the result

In application code, list the columns you need instead of *. The query stays correct when someone adds a column later, and it transfers less data.

WHERE: keep matching rows

SELECT id, amount
FROM orders
WHERE amount >= 50 AND status = 'paid';

SELECT name FROM customers WHERE city IN ('London', 'Berlin');
SELECT name FROM customers WHERE name LIKE 'D%';   -- starts with D
SELECT id FROM orders WHERE amount BETWEEN 20 AND 100;  -- inclusive

Text values go in single quotes. In standard SQL and PostgreSQL, double quotes mean an identifier such as a column name. That is why WHERE status = "paid" fails in PostgreSQL. MySQL treats double quotes as strings by default, and SQLite falls back to a string if no column has that name. Single quotes work in all three.

To test for a missing value, write IS NULL, not = NULL. See the article on NULL handling for why.

ORDER BY and LIMIT: sort and take the top rows

SELECT id, amount, created_at
FROM orders
ORDER BY amount DESC, id      -- biggest first, ties broken by id
LIMIT 3;

SELECT id FROM orders ORDER BY created_at DESC LIMIT 2 OFFSET 2;  -- the next page

Without ORDER BY, the database may return rows in any order, and that order can change between runs. Always sort when you use LIMIT.

LIMIT works in SQLite, PostgreSQL and MySQL. The SQL standard spells it FETCH FIRST 3 ROWS ONLY, which PostgreSQL also accepts. SQL Server uses TOP 3.

GROUP BY: one row per group

Aggregate functions such as COUNT, SUM, AVG, MIN and MAX collapse many rows into one value. With GROUP BY, you get one result row per distinct value of the grouping columns:

SELECT status,
       COUNT(*)              AS orders,
       SUM(amount)           AS total,
       ROUND(AVG(amount), 2) AS average
FROM orders
GROUP BY status
HAVING COUNT(*) > 1
ORDER BY orders DESC;

On the sample data, this returns a single row: paid with 3 orders. The pending and refunded groups have only one order each, so HAVING removes them.

The order the clauses run in

You write the clauses as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, but the database logically evaluates them in this order:

1. FROM (and any joins) picks the rows to start with
2. WHERE filters individual rows
3. GROUP BY forms groups
4. HAVING filters whole groups, so it can use aggregates
5. SELECT computes the output columns
6. ORDER BY sorts, then LIMIT cuts

This order explains two common surprises. You cannot use COUNT(*) in WHERE; use HAVING. And in PostgreSQL, an alias defined in SELECT is not available in WHERE, although it can be used in ORDER BY.

Every column in the SELECT list must either appear in GROUP BY or sit inside an aggregate. If PostgreSQL reports that a column “must appear in the GROUP BY clause”, see the matching Fix article.