Exercise #10

Find the printer models having the highest price. Result set: model, price.

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

  1. SELECT model, MAX(DISTINCT price)
  2. FROM Printer
  3. 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:

  1. SELECT MAX(price)
  2. FROM Printer;

However, the exercise also requires displaying the model(s) having the maximum price. Since we cant 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, were bound to use a subquery calculating the maximum price:

  1. SELECT model, price
  2. FROM Printer
  3. WHERE price = (SELECT MAX(price)
  4. FROM Printer
  5. );

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:

  1. SELECT model, price
  2. FROM Printer pr, (SELECT MAX(price) AS maxprice
  3. FROM Printer
  4. ) AS mp
  5. WHERE price = mp.maxprice;

However, this doesnt 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?


Solve this task at SQL-EX.RU

Bookmark and Share
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.