Overview

Introduction

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.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.