loading..
Ðóññêèé    English
15:22
go

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:

Console
Execute
  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:

Console
Execute
  1. SELECT MAX(price)
  2. 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:

Console
Execute
  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:

Console
Execute
  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 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

Tags
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
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.