SELECT, WHERE, ORDER BY, and LIMIT

Why this matters

Yesterday you put rows into a database. Today you get precise answers back out. A query that returns exactly the three columns and five rows you need is the difference between an API that stays fast at a million rows and one that loads the entire table into memory to find one pump.

By the end of the hour you can name the four clauses that shape almost every read in a real application — which columns, which rows, in what order, how many — and you will have written and verified six queries of your own.

Getting everyone to the same data

So that your output matches this lesson's, start from a known set of rows. Create db/seed.sql:

TRUNCATE maintenance_records, equipment RESTART IDENTITY CASCADE;

INSERT INTO equipment (name, serial_number, status, installed_on) VALUES
  ('Feed Pump 3',      'PMP-0003', 'operational', '2021-06-14'),
  ('Chiller 1',        'CHL-0001', 'operational', '2020-03-02'),
  ('Air Compressor A', 'CMP-0011', 'maintenance', '2019-11-20'),
  ('Feed Pump 4',      'PMP-0004', 'operational', '2022-08-05'),
  ('Conveyor B',       'CNV-0002', 'retired',     '2015-01-30');

INSERT INTO maintenance_records (equipment_id, performed_on, hours_spent, notes) VALUES
  (1, '2024-02-11', 1.50, 'Replaced mechanical seal'),
  (1, '2024-09-03', 0.75, 'Checked shaft alignment'),
  (2, '2024-05-19', 3.25, 'Coil clean and refrigerant top-up'),
  (3, '2025-01-08', 4.00, 'Rebuilt compressor valve plate'),
  (1, '2025-03-22', 2.00, 'Bearing replacement');

psql maintenance -f db/seed.sql

Projection: choosing columns

A SELECT names the columns you want. That choice is called projection.

SELECT name, status FROM equipment;

       name       |   status
------------------+-------------
 Feed Pump 3      | operational
 Chiller 1        | operational
 Air Compressor A | maintenance
 Feed Pump 4      | operational
 Conveyor B       | retired
(5 rows)

SELECT * means "every column". It is fine for exploring at the psql prompt and a poor habit in application code: it moves data you do not need over the network, and it silently changes shape the day someone adds a column. Name your columns.

You can rename a column in the output with AS, which matters later when two joined tables both have an id:

SELECT name AS equipment_name, installed_on AS commissioned FROM equipment;

Filtering: choosing rows

WHERE is the clause that filters rows. It takes a condition, and only rows for which the condition is true come back.

SELECT name, serial_number FROM equipment WHERE status = 'operational';

    name     | serial_number
-------------+---------------
 Feed Pump 3 | PMP-0003
 Chiller 1   | CHL-0001
 Feed Pump 4 | PMP-0004
(3 rows)

Note = for comparison. SQL is not JavaScript: a single = compares, and <> (or !=) means "not equal". Text values go in single quotes.

The conditions you will use constantly:

• Comparison: =, <>, <, >, <=, >=. They work on dates too: installed_on < '2021-01-01'.
• Combining: AND, OR, NOT. AND binds tighter than OR, so parenthesise when you mix them.
• Set membership: status IN ('operational', 'maintenance').
• Range: performed_on BETWEEN '2024-01-01' AND '2024-12-31' — inclusive at both ends.
• Pattern match: LIKE with % meaning "any run of characters" and _ meaning "any one character". ILIKE is the same but case-insensitive, which is what user search boxes want.
• Emptiness: IS NULL and IS NOT NULL.

SELECT name, installed_on FROM equipment
WHERE status <> 'retired' AND installed_on < '2021-01-01';

       name       | installed_on
------------------+--------------
 Chiller 1        | 2020-03-02
 Air Compressor A | 2019-11-20
(2 rows)

Sorting and limiting

ORDER BY sorts the result. ASC (ascending) is the default; DESC reverses it. LIMIT caps how many rows come back, and OFFSET skips rows first — together they are how the pagination you built in Week 6 is actually implemented.

SELECT id, equipment_id, performed_on, notes
FROM maintenance_records
ORDER BY performed_on DESC
LIMIT 3;

 id | equipment_id | performed_on |             notes
----+--------------+--------------+--------------------------------
  5 |            1 | 2025-03-22   | Bearing replacement
  4 |            3 | 2025-01-08   | Rebuilt compressor valve plate
  2 |            1 | 2024-09-03   | Checked shaft alignment
(3 rows)

The clauses are written in a fixed order — SELECT, FROM, WHERE, ORDER BY, LIMIT — and they are also applied roughly in that order: rows are found, filtered, sorted, and only then cut down. That is why LIMIT 3 gives you the three newest records rather than three arbitrary ones that were then sorted.

Walkthrough: from a question to a query

Requests arrive in English. Translate one clause at a time.

"Show the two most recently installed pumps that are not retired, newest first."

Start with the table and the columns — projection:

SELECT name, serial_number, installed_on FROM equipment;

Add the row filter. "Pumps" means the serial number starts with PMP-, and "not retired" is a second condition:

SELECT name, serial_number, installed_on FROM equipment
WHERE serial_number LIKE 'PMP-%' AND status <> 'retired';

    name     | serial_number | installed_on
-------------+---------------+--------------
 Feed Pump 3 | PMP-0003      | 2021-06-14
 Feed Pump 4 | PMP-0004      | 2022-08-05
(2 rows)

Then order and limit:

SELECT name, serial_number, installed_on FROM equipment
WHERE serial_number LIKE 'PMP-%' AND status <> 'retired'
ORDER BY installed_on DESC
LIMIT 2;

    name     | serial_number | installed_on
-------------+---------------+--------------
 Feed Pump 4 | PMP-0004      | 2022-08-05
 Feed Pump 3 | PMP-0003      | 2021-06-14
(2 rows)

Building it in stages is not slower — it is how you tell which clause broke when the answer looks wrong.

Your turn

Build db/queries.sql with at least six queries, each preceded by a comment stating the question in English and the row count you got.

1. Load db/seed.sql so your data matches this lesson.
2. Write a query listing every piece of equipment with status maintenance. Verify: 1 row.
3. Write one searching serial numbers for a fragment, using ILIKE and %. Verify it finds the right rows and misses the others.
4. Write one listing the five most recent maintenance records, newest first, using ORDER BY and LIMIT.
5. Write one combining two conditions with AND, over a date comparison and a status.
6. Write one using IN with three status values, then a sixth using BETWEEN on performed_on.
7. For one query, deliberately swap IS NULL for = NULL and record what comes back. Then fix it.
8. Save each query with its comment. Run the whole file with psql maintenance -f db/queries.sql and confirm every query still returns what your comment claims.

Common pitfalls

• Double quotes around text. WHERE name = "Chiller 1" gives ERROR: column "Chiller 1" does not exist, because double quotes mean an identifier. Text takes single quotes.
• WHERE after ORDER BY. The clause order is fixed. Out of order, you get syntax error at or near "WHERE".
• Filtering in JavaScript instead of SQL. Fetching all rows and using .filter() works on 5 rows and collapses on 500,000. Push the condition into WHERE.
• Trusting LIMIT without ORDER BY. It returns some rows, not the first rows.

Verify it yourself

Open today's reference, the PostgreSQL tutorial, and read its section on querying a table.

1. The tutorial shows DISTINCT, which this lesson skipped. Work out what it does and write a query using it over equipment.status. How many rows come back, and why?
2. This lesson claimed row order is undefined without ORDER BY. Find the documentation's own wording on that and record whether it agrees.

Add both to db/queries.sql as comments. Tomorrow you start changing rows, where a missing WHERE costs considerably more than a wrong answer.