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.
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:
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 6th row to the 9th 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!!