Typical mistakes at the solution of SELECT statement exercises

Database "Computer firm"

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 Printer
GROUP BY model

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

    Scheme

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

SELECT MAX(price) FROM Printer

    Scheme

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
               )

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

    Scheme

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

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

    Scheme

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