Row_Number(), Rank() and Dense

Row_Number(), Rank() and Dense_Rank()

Three Functions of SQL Server:
1) Row_Number() -- To get the Unique order of Rows
2) Rank() – To rank the Records with skipped Rows
3) Dense_Rank() – To rank the Records without skipping any Rows

Lets prepare base for these:
1) Create a table like this:
CREATE TABLE [dbo].[Student](
[StudentID] [int] IDENTITY(1,1) NOT NULL,
[StudentName] [varchar](50) NULL,
[Age] [int] NULL
) ON [PRIMARY]

2) Insert data into that like this:

3) Now run the below Query:
SELECT *
,ROW_NUMBER() OVER (ORDER BY age DESC) [Unique Order]
,RANK() OVER (ORDER BY age DESC) [Skipped Order]
,DENSE_RANK() OVER (ORDER BY age DESC) [Multiple Order]
FROM student

If u closely look at the above result set [Unique Order] column represent the Unique row number of each row which we got using Row_Number().
[Skipped Order] column represent the order of Rank which skips the number. This may be use in situation where u need to find out the rank of student based on marks, or rank of participants based on points..
[Multiple Order] column represents the intelligent Rank which does not skip the number. This may be used in situation where u need to select employees based on their salaries.
Difference between Rank() and Dense_Rank() is while assigning the next rank (after the common values rows) to the row RANK will consider the total count of rows before that row and DESNE_RANK will just give next rank according to the value.

I hope u understand what I tried to say..

Comments

Custom Effective Paging With Sorting across all pages GridView in Asp.Net

Hi, I need to comeback to blogging anyhow, since it was too long break. Hence yesterday i decided to start blogging again, and thought of this topic. When Microsoft shipped GridView control with Asp.net, it came with lot of new functionality, but it also lacking of existing functionality like 'VirtualCount' property which was available with DataGrid but not with GridView. Since because of this it is very much difficult to implement Custom Paging with Grid View. Now what is custom paging and default paging? To answer this question i will take an example. I need to display records on a page in tabular format, with paging and sorting. Well if you go with default paging and default sorting your development work is easy. But with it there is a drawback. - It is grid view control which decides what records to display and sorted by which column. So for that it need complete set of all records. Suppose if you want to display only 25 records, a...

Output Clause - New Feature of SQL Server 2005

SQL Server 2005 introduces a new TSQL feature that allows you to retrieve data affected by insert/update/delete statements easily. This is achieved by the use of OUTPUT clause which can reference columns from the inserted and deleted tables (that are available from triggers currently) or expressions. The OUTPUT clause can be used to return results to the client or consume it on the server into a temporary table or table variable or permanent table. Let us take a look at a common scenario now and how OUTPUT clause can be used to solve the problem. Use of identity column as primary key in a table is a fairly common practice. Additionally, if you have references to the identity column you need to know the value explicitly before inserting the related row. This scenario gets complicated if multiple identity values are generated as a result of inserting multiple rows. In this case, there is no easy way to determine the values that were inserted without using a trigger to populate a temporar...

Difference between @@Identity, Scope_Identity (), IDENT_Current(‘tablename’)

Problem: Which one of the following should be use to get the last Identity of the recently added row in T-SQL or Stored Procedure: @@Identity, Scope_Identity (), IDENT_Current(‘tablename’) Solution: So many times we required to get the last inserted rows identity to be insert them in child table for reference in stored procedure. All the above statements gives us the last identity inserted but in different perspective. So if you want to get the last identity in T-SQL or Stored Procedure always use Scope_Identity () to avoid problems in Multiuser (concurrent) scenarios. Let me explain why? Lets see what does each means? @@Identity : It returns the last IDENTITY value produced on a connection, regardless of the table that produced the value, and regardless of the scope of the statement that produced the value.@@IDENTITY will return the last identity value entered into a table in your current session. While @@IDENTITY is limited to the current session, it is not limited to the current...

Tar's Arena

Search This Blog