Exercise #10

Usually, users have no difficulties solving this exercise, but sometimes queries similar to the following one occur:

SELECT model, MAX(DISTINCT price)
FROM Printer
GROUP BY model;

The desire to solve the exercise without using subqueries is quite natural and understandable. If just the maximum price had to be displayed, no grouping would be needed, since the maximum would be sought for among all printers:

SELECT MAX(price)
FROM Printer;

However, the exercise also requires displaying the model(s) having the maximum price. Since we can’t include aggregate values along with nonaggregate ones in a SELECT clause unless we use the latter for grouping, it results in the aforementioned incorrect solution grouping the data by model. This solution returns the maximum price for each model; what we actually need is the list of models having the absolutely highest price (over the whole scope of printers).

Thus, we’re bound to use a subquery calculating the maximum price:

SELECT model, price
FROM Printer
WHERE price = (SELECT MAX(price)
               FROM Printer
               );

The subquery can include an  IN or >= ALL clause instead of a simple comparison operator (“=”).

Besides, the subquery can be placed in the FROM clause:

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

However, this doesn’t improve performance, since in both cases the subquery is executed once, after which the prices are compared for each row.

And yet – is there a way to solve the exercise without using a subquery?

T&S

Solve this task at SQL-EX.RU