loading..
Ðóññêèé    English
20:36
go

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
  1. SELECT model, MAX(DISTINCT price)
  2. FROM Printer
  3. GROUP 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
  1. SELECT MAX(price)
  2. 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
  1. SELECT model, price  
  2. FROM Printer  
  3. WHERE price = (SELECT MAX(price) 
  4.                FROM Printer
  5.                )

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

Bookmark and Share
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
month ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100