TOP(1) solution for “Subquery returned more than 1 value” error

If you connecting to a table using subquery, you might experience an error “Subquery returned more than 1 value“, that will crash your SQL.

Here is an elegant and powerful solution that

 (1) cures the error and

 (2) allows to locate your desired row more precisely.

For example, you need to get the latest price by many key fields with the latest date.  Additionally, you need to guarantee that your query will find one and only one price row even if there are multiple price entries on a given day.

Here is an SQL that might give you trouble:

SELECT orderType, region, item
, (SELECT price FROM tPrice PR1
        WHERE PR1.orderType = OT.orderType
               AND PR1.region = OT.region
               AND PR1.item = OT.item
               AND PR1.dateUpdated = (SELECT MAX(PR2.dateUpdate)
                       FROM tPtice PR2 WHERE …)) [Price]
FROM tOrderTypes OT
WHERE item != 0

When you are connecting to tPrice table, there is a possibility that your subquery will return more than one price row, if this order type and item has more that one price record in one day. In that case your SQL will crash and that could happen not today, but some future day when your solution is already in production.

Consider this modification that not only solves your error massage “Subquery returned more than 1 value”, but also will allow you to locate your latest price record more precisely:

SELECT orderType, region, item
, (SELECT TOP(1) price FROM tPrice PR
        WHERE PR.orderType = OT.orderType
               AND PR.region = OT.region
                AND PR.item = OT.item
        ORDER BY dateUpdate, timeUpdated, priceID) [Price]
FROM tOrderTypes OT
WHERE item != 0

Let discuss the statement above:

 — TOP(1) guarantees that your subquery will only return one row.

 — ORDER BY allows subquery to pick just the right row.  You can use a variety of sort order combination and this is easily adjustable and very readable.

Credits: the idea for this solution was discovered in a book “T-SQL Querying” on page 191.  A truly masterful solution.  Thank you!

[Written 2022-01-29 SA 16:00 EST]