Consider a relation book (title, price) which contains the titles and prices…

Concept: A correlated subquery that counts, for each row of a table, how many rows of the same table have a strictly greater value in some column is a standard way to express relative rank in SQL without window functions. If that count of strictly-greater values is required to be less than k, exactly the rows ranked 1 through k (by that column) satisfy the condition — provided the compared values are all distinct, so counting has no ties to worry about.

Application (this query):

1. For reference, the query is: SELECT title FROM book AS B WHERE (SELECT COUNT(*) FROM book AS T WHERE T.price > B.price) < 7;

2. For a given book B, the subquery SELECT COUNT(*) FROM book AS T WHERE T.price > B.price counts exactly how many OTHER books are priced strictly higher than B.

3. The outer WHERE clause keeps B only when that count is less than 7 — i.e., at most six books are priced higher than B.

4. 'At most six books priced higher than B' is exactly the definition of B's price rank being 1st through 7th (rank = 1 + the count of strictly higher-priced books).

5. Since no two books share a price, ranks are well defined, and (assuming the table has at least seven rows) exactly seven distinct books satisfy this rank condition — so the query returns the titles of those books.

Cross-check: An equivalent, order-based formulation of the same query is SELECT title FROM book ORDER BY price DESC LIMIT 7, which directly asks for the seven highest-priced rows — it returns the same set of titles as the correlated-count version above, confirming the result.

Result: Assuming the table has at least seven books (the standard reading for this classic query), the query returns the titles of the seven most expensive books.

Note: unlike the ORDER BY ... LIMIT 7 formulation, the correlated-subquery version does not itself order its output — both return the same seven rows, but only the ORDER BY version guarantees an ordering by price.