Asking a table
A café keeps its menu in a table called drinks. Each row is one drink. The columns are id, name, kind and price. SELECT is how you ask a table a question.
The café app wants the two most expensive coffees under 4.00. In SQL that is one SELECT statement made of clauses: parts that each start with a keyword. The app sends it to the DBMS as plain text.
You write SELECT first, but the DBMS does not run it first. It starts with FROM, then WHERE, then SELECT, then ORDER BY, and LIMIT comes last. The numbers on the card show that order.
Running the clauses
FROM names the table to read. The DBMS starts with all 7 rows of drinks, with every column. The other clauses only ever remove, choose or sort what FROM gave.
WHERE tests every row. AND means both tests must be true. Text goes in single quotes: 'coffee'. Chai latte is under 4 but is tea; Mocha costs 4.20. 4 of 7 rows pass.
Now SELECT keeps only the columns asked for: name and price. id and kind are left behind. The 4 kept rows go on as two columns. SELECT * would keep every column.
ORDER BY sorts the rows. DESC means descending: highest price first. ASC, the default, means lowest first. Latte (3.80) moves to the top and Espresso (2.50) to the bottom.
LIMIT 2 keeps the first 2 rows and drops the rest. It runs last, after the sort, so those 2 are the most expensive: Latte and Flat white.
The answer
The DBMS sends the app a small table: Latte 3.80 and Flat white 3.70. The query said what it wanted; the DBMS worked out how to find it.
Leave out ORDER BY and SQL promises no order at all. LIMIT 2 then returns any 2 of the 4 coffees, for example Espresso and Latte, the first ones found. When order matters, say ORDER BY.
What happens
;; step 1/10 · table table drinks column id type integer column name type text column kind type text column price type numeric(4,2) id name kind price 1 Espresso coffee 2.50 2 Latte coffee 3.80 3 Green tea tea 2.20 4 Mocha coffee 4.20 5 Chai latte tea 3.90 6 Flat white coffee 3.70 7 Cappuccino coffee 3.50 7 rows x 4 columns numeric(4,2): up to 4 digits, 2 after the point
FROM, WHERE, SELECT, ORDER BY, LIMIT is the logical order the PostgreSQL SELECT reference describes. The query planner may do the work in another way (for example using an index), but the answer is always the one this order gives. If two rows had the same price, ORDER BY price would not say which comes first. Add a second sort column, such as ORDER BY price DESC, id, to make the order complete.