CROSS APPLY / OUTER APPLY page 2
Let's imagine that along with maximal price, you need minimal price, average price and so on. Correlated subquery in SELECT clause must return only single value, so we are forced to duplicate sql-code for each aggregate:
But when using CROSS APPLY, we simply add into subquery a wanted aggregate function as a new column:
Combine each row in the Laptop table with next row in the order of sorting by (model, code).
The code column is used in sorting to get single-valued ordering for rows with the same value in the model column. We'll use CROSS APPLY operator to pass into subquery the parameters of a current row and take the first row under this current row in the given sorting. So,
Try to solve this problem using standard means and compare the efforts required.
OUTER APPLY operator
As the results of above query have shown, we "lost" the last (sixth) row in Laptop table because there is no row to
Replacement CROSS APPLY by OUTER APPLY illustrates above said.
Another popular problem is to output the same number of rows for each group, for example when it is needed to advertise the 5 most popular goods within each category. Let's consider the following exercise.
From Product table, retrieve for each group characterized by the type of the product three models with the lowest numbers.
Solutions to this problem on the site sql-ex.ru we'll supplement with another one solution using CROSS APPLY.