Select, filter, and sort

⏱ 25 min

Hook

The support lead messages you before standup: "We can only expedite a few orders today — which pending ones are worth the most?" The answer is in the orders table, but that table has thousands of rows and a dozen columns, and almost none of them matter for this question. Your job is to reach in, pull out just the pending orders, rank them by value, and hand back a short list the support team can act on in the next ten minutes.

Concept

Every analytical question you answer from a single table is some combination of three decisions: which columns you want, which rows you want, and in what order you want them. SQL has one clause for each.

• SELECT chooses the columns — the attributes you care about.
• WHERE filters the rows — the records that match a condition.
• ORDER BY sorts the result — so the most important rows rise to the top.

A query is a question you write in SQL; the database reads it and returns a result set, which is just another table. Think of the three clauses as a sentence: "Show me these columns, for these rows, sorted this way."

SELECT column_a, column_b
FROM table_name
WHERE condition
ORDER BY column_a DESC;

The database does not run this top to bottom the way you read it. It starts from FROM (which table), applies WHERE (keep matching rows), then SELECT (keep chosen columns), and finally ORDER BY (sort what remains). Knowing that order explains a rule that surprises beginners: WHERE filters rows, so it runs before the result is shaped, and it can only test raw columns — never a summary like a total. Summaries come in the next lesson.

A few building blocks you will use constantly inside WHERE:

You want	Write
An exact match	status = 'pending'
A numeric comparison	amount > 50
Two conditions, both true	status = 'pending' AND amount > 50
One of several values	category IN ('books', 'home')
Sort largest first / smallest	ORDER BY amount DESC / ASC

Text values go in single quotes ('pending'); numbers do not. ORDER BY defaults to ascending, so you add DESC when the biggest number is the most interesting — which, for "worth the most," it is. To cap a long result at a short list, add LIMIT.

🧠 Knowledge check

1. A stakeholder wants only shipped orders, most recent first. Which clause decides which rows are kept?

a) SELECTb) WHEREc) ORDER BYd) LIMIT

2. Which WHERE clause keeps pending orders worth more than $50?

a) WHERE status = pending AND amount > 50b) WHERE status = 'pending' OR amount > 50c) WHERE status = 'pending' AND amount > 50d) WHERE status = 'pending' AND amount > '50'

Worked example

Let me answer the support lead's actual question end to end. The orders table has these columns: order_id, customer_id, category, amount, status, and order_date. The lead wants the highest-value pending orders, and only needs enough to act — say the top three.

I make each decision in turn. Columns: the team needs the order id (to look it up), the amount (to justify the priority), and the date (to see how long it has waited) — not the other nine columns. Rows: only status = 'pending'. Order: biggest amount first, so the most valuable order is line one. Cap: three rows.

SELECT order_id, amount, order_date
FROM orders
WHERE status = 'pending'
ORDER BY amount DESC
LIMIT 3;

order_id  amount  order_date
1003      200.0   2026-06-22
1001      120.0   2026-06-05
1005      90.0    2026-06-25

That result is the answer, and I can state it in one sentence: "Expedite order 1003 first ($200), then 1001 and 1005." Notice what the query left out on purpose. It did not return shipped or cancelled orders, because they cannot be expedited. It did not return every column, because the team does not need them. A good analytical query is as much about what you exclude as what you include — the tighter the result, the faster the decision.

Hands-on

Your turn, with a different question from a different stakeholder. The exercise below loads a products table for an online store. The merchandising manager wants to know which in-stock products in the electronics category are priced above $50, listed most expensive first, so they can feature them in a promo. Write the SELECT / WHERE / ORDER BY query that returns them.

▶ SQL Kingdomda101-select-filter-sort-featured-products

Practice in SQL Kingdom ↗

Success criteria: your result shows only electronics priced over $50 that are in stock, sorted by price descending. SQL Kingdom checks your result against the expected rows and awards XP when it matches.

Recap

• You can pick exactly the columns a question needs with SELECT instead of returning everything.
• You can narrow thousands of rows to the handful that matter with WHERE, quoting text and comparing numbers correctly.
• You can rank a result so the most important row is first with ORDER BY ... DESC, and cap it with LIMIT.

Next up: what to do when the stakeholder does not want individual rows at all, but a single summary number — total revenue, order counts, averages. That is aggregation and GROUP BY.

🧠 Knowledge check

1. By default, does ORDER BY amount sort largest-first or smallest-first?

a) Largest firstb) Smallest first (ascending)c) Random orderd) It refuses to run without DESC or ASC

2. Why can't WHERE filter on a total like SUM(amount) in this lesson's queries?

a) WHERE only works on text columnsb) WHERE filters individual rows before any summary is computedc) Totals must be filtered with ORDER BYd) SUM is not valid SQL

📌 Key takeaways

• A single-table query is three decisions: columns (SELECT), rows (WHERE), and order (ORDER BY).
• Quote text values, leave numbers unquoted, and combine conditions with AND / OR / IN.
• WHERE filters raw rows before any summary exists, so it cannot test a total.