Deriving tables and selecting top n records
As a comparison to DAX, SQL is a mature language that has a variety of mechanisms for temporarily defining and using tables. One of the reasons for using this type of feature is that a result may need to be pre-computed before it is applied in the outer constructs of a query.
Consider the situation of accumulating sales based on the sales values' ranks, as shown in the following screenshot:
This type of query must pre-compute values before they can be used. Logically, the query must determine each customer's sales value, then rank the customers based on that value, and then (finally) determine (on a row-by-row basis) the total value of sales for all rows with a lower rank. Clearly, there is an order to implement this type of query because one set of values cannot be calculated before the other is complete. Using temporary structures is an excellent method for achieving this.
Unfortunately, there is no declaration to derive a temporary table...