1. Increasing window complexity
In this lesson we are going to take a look at the functions we looked at earlier and apply some new concepts to get some different results.
2. Reviewing aggregations
Remember this code from the first video of the chapter? Here we are calculating one total for each window or each SalesYear and there is no ORDER BY because unlike specific window functions like LEAD, ORDER BY is not required.
3. Adding ORDER BY to an aggregation
If you take that same query and add ORDER BY SalesPerson clause into the window, the results change. Now the values in the CurrentQuota column are totaled by SalesPerson and SalesYear. Within SalesYear, every time SalesPerson changes, the total changes.
4. Creating a running total with ORDER BY
Every time you order by a different column, the results change. In this query, the ModifiedDate is different for each row, and thus the result is a running total.
To create a running total, ORDER BY the column that is different for each row in the window.
5. Adding row numbers
In certain situations, you want to assign row numbers to each row in a window. You can do this using the ROW_NUMBER function. Similar to LEAD and LAG, the keyword ORDER_BY is required when using ROW_NUMBER.
6. Adding row numbers in T-SQL
Here we use the ROW_NUMBER to assign a row number for each row in the window. The window is partitioned by SalesPerson and ordered by SalesYear.
7. Let's practice!
Great! Go ahead and create running totals and assign row numbers.