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