Windowing and Ranking in SQL Server – Part 1

Hello Folks,

You must have heard about this feature which was introduced earlier in SQL Server 2005. Although since then there hasn’t been any change with arrival of SQL Server 2008 R2.

The windowing provides a new perspective on the data, while the ranking functions then use that perspective to provide additional ways to manipulate the query results.

Windowing:

The Windows have to be applied before the Ranking functions in the Query. It becomes easier to think through the window and then add the ranking function.

OVER () Clause:

  • The OVER () clause creates a new window on the data. Like an independent ordering of the rows, which may or may not be of the same sort order of the ORDER BY clause. 
  • The windowing capabilities create an alternate flow to the query with its own sort order and ranking functions. 
  • In fact the results of the windowing and ranking are passed back into the query before the ORDER BY clause. 
  • The complete syntax is: 

OVER (ORDER BY columns)

  • The “columns” can be any available column or expression, as in ORDER BY clause. But the OVER () clause won’t accept a column ordinal position, like 1, 2 etc., as it would be in the ORDER BY clause. 
  • It has the advantage of the indexes and can be very fast, even if the sort orders are different from the main query’s sort order. 
  • This will become much clear after seeing this example: 

First, you should see the table that we are using in this example-

1_SQL_Server_Windowing_and_Ranking_Part1

Now, the OVER () clause creates a separate view to the data sorted by Salary;

USE TEST
SELECT ROW_NUMBER() OVER(ORDER BY Salary) AS RowNumber,
Fname,Class, Salary
FROM Students
ORDER BY RowNumber

The result can be seen as:

   

2_SQL_Server_Windowing_and_Ranking_Part1

You can see from the above that Salary are sorted, an according to that Row Number are incremented.

Using Partitioning:

  • The OVER () clause normally creates a single sort order, but it can divide the windowed data into partitions, which are similar to groups in an aggregate GROUP BY query.
  • They are very powerful because the ranking functions will be able to restart with every partition.
  • The following example will make you more clear, of what this is all about:

I have used the same table as shown above, “Students”.

Now, I am showing how the Partitions work with the OVER () clause. Here, i am making a separate partition for each “Class”, and the “RowNumber” will increment according to it;

SELECT ROW_NUMBER() 
OVER(PARTITION BY Salary ORDER BY Salary) AS RowNumber,
Fname,Class, Salary
FROM Students
ORDER BY Salary

The results of the above query can be seen as:

3_SQL_Server_Windowing_and_Ranking_Part1

You can also use the above Partition with the Aggregate functions.

Well this was all about the Windowing and in the next part I will deal with Ranking functions i.e., how it comes into the play and for what?

So keep in touch!

Hope you got it understood well 🙂

And also comments on this!!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

   

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

2 Comments on “Windowing and Ranking in SQL Server – Part 1”

Leave a Reply

Your email address will not be published.