Archive

First of all I would like to wish my readers a very Happy and Prosperous New Year – 2013!!!

This is my 1st post in this new year and I will try to come up with more informative and interesting topics related to SQL Server.

Coming back to the topic, sometime back in Part-1 [link] I discussed about how to use both PARTITION BY & ORDER BY clause within OVER() clause when using it with AGGREGATE functions, like SUM().

Here in this post we will see other features that are added to the OVER() clause.

ROWS/RANGE are new the 2 new clauses that you can use inside the OVER() clause to limit the records within the Partition by specifying start and end within the partition. They use a new set of keywords to define the boundaries within the partition, i.e. PREECEDING, FOLLOWING mixed with UNBOUNDED or CURRENT, or number of rows. The PRECEDING and FOLLOWING rows are defined based on the ordering in the ORDER BY clause of the query.

#1. Using ROWS/RANGE UNBOUNDED PRECEDING with OVER() clause:
Let’s check with 1st example where I want to calculate Cumulative Totals or Running Totals at each row. This total is calculated by the SUM of current and all previous rows.
We will use UNBOUND PRECEDING option here, which means that the window starts from the 1st row till the current row.

In the Output above you can see the was the SUM is calculated, as we go down all previous and current rows are getting summed at all levels.
Here you can also see a difference between the way ROW & RANGE are calculated, for SalesPersonID=4:
– ROWS shows different and continuous calculation at different levels.
– But RANGE shows same calculation of all 3 records at different levels.

#2. Using ROWS/RANGE CURRENT ROW & UNBOUNDED FOLLOWING with OVER() clause:
Now, let’s say you’ve to do the same calculation, but in reverse order. So, here we will use BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING options to define our Window. This means the window starts from current row and ends at the last row.

-- To Calculate Cumulative SUM or Running Totals, but in REVERSE order:
;WITH CTE AS (
SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
FROM [HumanResources].[EmployeePayHistory]
WHERE BusinessEntityID <= 10
)
SELECT SalesPersonID, SalDate, Salary
,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRows
,SUM(Salary) OVER (ORDER BY SalesPersonID RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ReverseCumulativeSumByRange
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:

In the Output above we can see that the Running totals are now in reverse order, compared to the 1st example.
Also, the ROWS & RANGE difference can be seen for the SalesPersonID=4 similar to the 1st example.

#3. Using ROWS/RANGE PRECEDING & FOLLOWING with OVER() clause, without UNBOUNDED:
Now, if you want to calculate the SUM only of those values that are just adjacent to a current row. Here we will use combination of row numbers with CURRENT/PRECEDING & FOLLOWING keywords.
Let’s us check this by following example:

-- To Calculate Moving SUM, by taking 3 moving rows:
;WITH CTE AS (
SELECT BusinessEntityID AS SalesPersonID, CAST([Rate] AS DECIMAL(10,0))AS Salary, [ModifiedDate] AS SalDate
FROM [HumanResources].[EmployeePayHistory]
WHERE BusinessEntityID <= 10
)
SELECT SalesPersonID, SalDate, Salary
,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingSumByRowsPrevCurrentNext
,SUM(Salary) OVER (ORDER BY SalesPersonID ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING) AS MovingSumByRowsCurrentNext2
FROM CTE
ORDER BY SalesPersonID, SalDate

OUTPUT:

In the output above for:
– Column MovingSumByRowsPrevCurrentNext: it calculates the SUM of just one Previous, Current & one Next row.
– Column MovingSumByRowsCurrentNext2: it calculates the SUM of Current and next two rows.

So, by above 3 examples we saw how to use new ROWS & RANGE clauses with a mix of UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, CURRENT ROW, etc. Similarly there could be many different cases and scenarios where we can use there clauses and options.

I will try to post more on these features as I see anything new and exciting around this.

A CTE (Common Table Expression) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

SQL Server 2005 provides functionality for using Ranking Functions with your result set. One can select a number of Ranking algorithms which are applied to a column of your table that you want to classify in a scope of your executing query. This feature is Dynamic and upon change of data (addition or removal of rows) it gives desired results the next time query is run.

–> Its 4 gems are:

1. ROW_NUMBER: Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax:

ROW_NUMBER() OVER ( [ < partition_by_clause > ] < order_by_clause > )

2. RANK: Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.
Syntax:

RANK() OVER ( [ < partition_by_clause > ] < order_by_clause > )

3. DENSE_RANK: Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.
Syntax:

DENSE_RANK() OVER( [ ] < order_by_clause > )

4. NTILE: Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
Syntax:

NTILE(integer_expression) OVER( [ ] < order_by_clause > )

–> Now lets take an example, simple one of a class of students, their marks & class:

–> Here:– RowNum column lists unique ID’s of students, like Roll Numbers.– Rank lists student rank with equal ranks those secured equal marks, thus there is no 2nd or 3rd.– DenseRank lists student ranks with no gaps, so here 3 students came 1st &2nd and only 2 3rd.– nTile listed students in different but equal groups, can be thought of as different sections.

–> Now, lets use the PARTITION BY option, its same as group by clause. Lets group/partition the students group by their classes A&B:

select class, marks, stuName,
ROW_NUMBER() over(partition by class order by marks desc) as [RowNum],
RANK() over(partition by class order by marks desc) as [Rank],
DENSE_RANK() over(partition by class order by marks desc) as [DenseRank],
NTILE(3) over(partition by class order by marks desc) as [nTile]
from #tempTable