Windowing and Ranking in SQL Server – Part 2

Hello Folks,

You must have heard about this feature which was introduced earlier in SQL Server 2005. And also you had seen my previous article on Windowing. If you want to refer it again, so here’s the link;

This article post is mainly being based upon Ranking Functions.

Ranking Functions:

The windowing capability is not useful without we specify with Ranking functions. So, that’s how Ranking functions comes into the play. In fact there are some ranking functions as follows:

• ROW_NUMBER
• RANK
• DENSE_RANK
• NTILE

We will discuss each of these one by one:

ROW_NUMBER () function:

• The ROW_NUMBER () function generates an auto-incrementing integer according to the sort order of the OVER () clause.
• It returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
• The row number function simply numbers the rows in the query result, and there’s absolutely no correlation between the physical address and actual row numbers.
• The syntax can be seen as-

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

• One of the practical usage of the ROW_NUMBER () function could be to filter by the row number values for pagination.
• This will be much more clear to you after seeing this example:-

I have used “Students” table from “Test” database;

Suppose from these I want to see all the Students sorted as per their Salary and then as per their S_Id, so the query will be:

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

The result for this will be shown as:

Now, if I want to see only 4 records, i.e. , from 6^th row to the 9^th row, then I will surely try to filter the above table using a WHERE clause;

USE TEST
SELECT ROW_NUMBER() OVER(ORDER BY Salary,S_Id) AS RowNumber,
Fname,Class, Salary
FROM Students
WHERE ROW_NUMBER() OVER(ORDER BY Salary,S_Id)
BETWEEN 6 AND 9
ORDER BY RowNumber

Error comes! What goes wrong in the above code?

Msg 4108, Level 15, State 1, Line 5

Windowed functions can only appear in the SELECT or ORDER BY clauses. 

Now what does this means? Is it indicating that we won’t be able to use OVER () clause in the WHERE condition? If it’s yes, then how will go out to filter this table?

Well don’t’ worry, you will get an answer for every question that might be arising on your head.

If this might be the case, then you have to use a Subquery for this. This means you have to declare the Windowed functions inside the Subquery itself.

So the query for this will be:

USE TEST
SELECT RowNumber,FName,Class, Salary
FROM ( 
        SELECT ROW_NUMBER() OVER(ORDER BY Salary,S_Id) AS RowNumber, FName, Class, Salary
        FROM Students
     ) AS Q
WHERE RowNumber BETWEEN 6 AND 9
ORDER BY RowNumber

The result can be seen as:

So this was all about ROW_NUMBER function, in the next section I would like to deal with the remaining Ranking functions (rank, dense_rank, ntile).

So be tuned!

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