Lessons · SQL · comparing against one computed value

A query that returns one value

A subquery that returns exactly one row and one column can be used anywhere a value can: in a comparison, a SELECT list, a WHERE.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Above the average, below the maximum, the second highest: comparisons against a statistic of the whole table are the everyday use of a scalar subquery.

How to think about it

Write the statistic as its own SELECT, check it returns one value, then put it in parentheses where the value belongs. For 'second highest', ask for the maximum that is less than the maximum.

Worked example

SELECT MAX(amount) FROM orders WHERE amount < (SELECT MAX(amount) FROM orders);

The second-highest amount: the biggest one that is not the biggest.

SELECT id, amount FROM orders WHERE amount > (SELECT AVG(amount) FROM orders);

Orders above the overall average.

Your turn

Products priced above the average price.

SELECT name FROM products WHERE price > (SELECT (price) FROM products);

The trap

A scalar subquery that returns two rows is an error in most databases; SQLite quietly takes the first. Make sure it can only return one.

Practise comparing against one computed value on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.