Brightlane's procurement team is looking for the lowest-priced product in the catalogue.
Write a query to return the name and price of every product whose price equals the minimum price in the catalogue.
Assumptions:
- The
productstable contains every product in the catalogue. - More than one product may share the minimum price — the result should include all tied products, not just one.
Output:
- One row per product at the minimum price, with columns
nameandprice.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
name,
price
FROM
products
WHERE
price = (
SELECT
MIN(price)
FROM
products
)The shape
(SELECT MIN(price) FROM products) resolves to the single lowest price in the catalogue — 12.99 — and the outer WHERE keeps every product whose price equals it. Two products share that minimum, and both come back: HDMI Cable 2m and Picture Frame 8x10.
Clause by clause
FROM productsis the source set: every row in the catalogue.WHERE price = (SELECT MIN(price) FROM products)does the matching. The subquery runs once, returns one number — the catalogue minimum — and the outer comparison readsprice = 12.99for every row. Any product priced exactly at the minimum passes; everything else drops.SELECT name, pricereturns the two columns the procurement team needs to identify each lowest-priced item.
Why this and not a sort with LIMIT 1
The instinct on a "find the lowest-priced product" question is often to sort the table and take the top row. That works when exactly one product holds the minimum. When two or more products are tied at the minimum — as they are here — LIMIT 1 returns only one of them, picked arbitrarily by the planner. The other tied products silently disappear from the result.
The scalar-subquery form is tie-aware by construction. The subquery resolves the minimum once, then WHERE = returns every row that matches that value. Both tied products come through. For any question of the form "which rows are at the boundary," this is the shape that handles ties correctly without extra logic on top.
You practiced WHERE col = (SELECT MIN(col) FROM same_table) to find rows at an aggregate boundary. The recurring trade-off: LIMIT 1 returns only one row even when there's a tie; the scalar-subquery form returns every tied row, which is usually what you actually want.
