Exercise #10 |
||
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? To solve the problem on SQL-EX.RU |