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:

  • RANK

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:

The result for this will be shown as:


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

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:

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!!



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