Find the printers having the highest price. Result set: model, price.
The problem usually does not cause difficulties, however, sometimes I receive by email solutions similar to the following one:
Clearly natural desire to solve a problem without subqueries. If it was required to deduce only a highest price, the grouping would be unnecessary as the maximum would be on all set of printers:
However this exercise requires to deduce also number (numbers) of the model having a highest price. As we cannot use in SELECT clause the aggregate values alongside with detailed ones (without grouping by the detailed values), as a result we get the above wrong solution that use grouping by model. This solution gives a highest price over each model number, but we need to receive models, which have absolute (over all set of printers) highest price.
So, we need to use a subquery which calculates highest price:
In so doing, the subquery can be entered not only with the simple comparison operator («=»), but also with IN or >=ALL clauses.
We can use subquery in the FROM clause also:
However it does not give a gain in performance because the calculation of a subquery is carried out only once in any case, thereafter the comparison of the prices for every row will be made.
But still, whether it is possible to solve a problem without a subquery?