06:13

# 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:

Console
Execute
`SELECT model, MAX(DISTINCT price)FROM PrinterGROUP BY model`

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:

Console
Execute
`SELECT MAX(price)FROM Printer`

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:

Console
Execute
`SELECT model, price  FROM Printer  WHERE price = (SELECT MAX(price)                FROM Printer               )`

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:

Console
Execute
`SELECT model, price FROM Printer pr, (SELECT MAX(price) AS maxprice                    FROM Printer                  ) AS mp WHERE price = mp.maxprice`

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?

T&S

To solve the problem on SQL-EX.RU