The Implementing Aggregate Queries module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:
- Analytic Functions
- Work with Ranking Functions
Lab time: It will take approximately 30 minutes to complete this lab.
Exercise 1 - Analytic Functions
In this exercise, you will learn the following:
Work with analytic functions CUMEDIST, FIRSTVALUE, LAG LASTVALUE , LEAD, PERCENTILECONT, PERCENTILEDISC, PERCENTRANK Work with RANK, NTILE, DENSERANK, ROWNUMBER Ranking functions
Exercise 2 - Work with Ranking Functions
SQL Server provides you with a set of ranking functions using which you can rank the rows in the result set of your query. You can use these ranking functions in your SELECT statement and assign a rank to each row in the result set.
The four different ranking functions that SQL Server 2012 provides include:
ROW_NUMBER: This is the most basic ranking function. This function numbers each row in the partition of a result set in a sequential order starting from one for the first row in a partition. If the result set is not partitioned, this function numbers the rows in the result set sequentially treating them as a single group.
RANK: This function returns the rank of each row in the partition of a result set. Unlike the ROW_ORDER function that ranks the row sequentially, this function ranks a row by one plus the number of ranks that comes before that row. Also, when two or more rows have a same value (tie), this function ranks the tied row with the same rank.
DENSE_RANK: This function is similar to the RANK function. However, the DENSE_RANK function is not tied to the row numbers in the result set. In other words, it returns the rank of the rows within a partition in a result set without any gaps in the ranking. This function calculates the rank of a row by one plus the number of distinct ranks that come before that row.
NTILE: This function allots the rows in an ordered partition of a result set into a specified number of groups. It then ranks a row within a group based on the specified column.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.