Scalar Subquery Filter — Solution & ExplanationProblems / Scalar Subquery Filter / EditorialApproach

Compute the maximum as a scalar subquery: (SELECT MAX(price) FROM products) returns a single value.

Filter with it: WHERE price = (SELECT MAX(price) FROM products) keeps every row whose price equals that value — ties included.

Order by product_id.

In PySpark, the same idea is expressed by aggregating the max into a one-row DataFrame (products.agg(F.max("price"))), crossJoin-ing it onto every row (a one-row cross join is a cheap broadcast), and filtering where the prices match.Key InsightORDER BY price DESC LIMIT 1 looks equivalent but silently drops tied rows — here two products share the top price, so LIMIT 1 returns the wrong answer. A scalar-subquery comparison is the tie-safe way to say "all rows at the extreme value", and the same pattern generalizes to minimums, averages, and per-group extremes with correlated subqueries.Hints

You need the single maximum price first — (SELECT MAX(price) FROM products) computes it as one scalar value.

Use that subquery directly in the WHERE clause: WHERE price = (SELECT MAX(price) FROM products). The database evaluates the subquery once and compares every row against it.

Don't reach for ORDER BY price DESC LIMIT 1 — it returns only one row even when several products tie at the top.

In PySpark there is no scalar subquery, so broadcast the one-row aggregate with crossJoin and filter on equality.

Solve Scalar Subquery Filter yourself →