Rank() and Row_Number() Functions

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Rank() and Row_Number() Functions

Author

Topic

diamond5
Starting Member

15 Posts

Posted - 2010-07-12 : 07:45:27

hello;
what defferince between rank and rownumber functions?

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-07-12 : 08:22:55

When ever the 2 values found with same value, rank() will assign same number to them... where as Row_number() will assign 2 different successive numbers to them...

see the below script... u can see, the rank() assigned same value 2 for the 2 records havign the value 35.
declare @t table
(
ID int
)

insert into @t
select 15 union all
select 35 union all
select 35 union all
select 45

select Id,ROW_NUMBER() over ( order by ID) from @t

select Id,rank() over ( order by ID) from @t

diamond5
Starting Member

15 Posts

Posted - 2010-07-12 : 08:58:43

thanks for alot ddramireddy ..
what defferenc between DENSE_RANK() and Rank()??

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2010-07-12 : 09:10:10

Its like Suppose, In a class, 3 students write the exam and u are assigning ranks to them. First 2 students got same marks.

So, First 2 Students will get No.1 Possition
The above is same for Rank() and Dense_rank()

Then the Third student will get No.3 position if we use Rank(), if we use DEnse_rank(), he will get No.2 position.

diamond5
Starting Member

15 Posts

Posted - 2010-07-13 : 02:58:12

thank u eng.ddramireddy for this answer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources