How to break a tie using Rank() function in SQL

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 2005 Forums

Transact-SQL (2005)

How to break a tie using Rank() function in SQL

Author

Topic

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 00:57:42

I am using rank() function in SQL. I want to know, how to break the tie in SQL server 2005.

If anybody knows, please reply

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:01:07

didnt get that. can you explain? do you want to give them seperate rank. then use ROW_NUMBER(). if you want to use continuos ranks use DENSE_RANK

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 01:03:33

For example
I am doing rank for Salary. If two persons salary is equal then i need to do the rank based on the another field HierarchyID(like 1,2,3 in my requirement).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:06:14

quote:
Originally posted by Govilakshmi

For example
I am doing rank for Salary. If two persons salary is equal then i need to do the rank based on the another field HierarchyID(like 1,2,3 in my requirement).

then what you want is ROW_NUMBER() with PARTITION BY. look for syntax and usage in books online.

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 01:18:35

As per my understanding the Row_Number and Partition will reset the continous rank.

How it will help in this scenario?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 01:35:35

quote:
Originally posted by Govilakshmi

As per my understanding the Row_Number and Partition will reset the continous rank.

How it will help in this scenario?

show your current sample data and rank you expect it to get

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 01:54:49

The rank its showing currently is
Value Rank
582 4
3534 3
16707 1
16707 1
6917 2

But i have Hierarchy id in another table. So, if tie is happened then i need to calcualate the rank based on the Hierarchy. For example, if the hierarchy id is 1 then the rank should be high, if the hierarchy is 2 then the rank should be lesser

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 02:02:58

quote:
Originally posted by Govilakshmi

The rank its showing currently is
Value Rank
582 4
3534 3
16707 1
16707 1
6917 2

But i have Hierarchy id in another table. So, if tie is happened then i need to calcualate the rank based on the Hierarchy. For example, if the hierarchy id is 1 then the rank should be high, if the hierarchy is 2 then the rank should be lesser

ok then what you can add hierarchyid also in order by clause of rank function.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 02:18:50

as an example:-


declare @table table
(
val numeric(10,2),
hid int
)
insert into @table
select 111.22,1 union all
select 65.42,1 union all
select 13.20,1 union all
select 105.22,1 union all
select 105.22,2 union all
select 100.02,1 union all
select 125.12,2 union all
select 110.00,1 

select rank() over (order by val,hid) as ran,*
from @table

output
---------------
ran                  val                                     hid
-------------------- --------------------------------------- -----------
1                    13.20                                   1
2                    65.42                                   1
3                    100.02                                  1
4                    105.22                                  1
5                    105.22                                  2
6                    110.00                                  1
7                    111.22                                  1
8                    125.12                                  2

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 02:46:25

Thanks visak
I have to use Hid only when the rank is in tie. Otherwise, no need to calculate rank based on the Hid.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 02:52:51

can you give some sample data to illustrate this. didnt get what you're asking for?

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-19 : 02:58:48

For example rank is showing like following

Name Value Rank
A 582 4
B 3534 3
C 16707 1
D 16707 1
E 6917 2

In another table i am having Hierarchy for these name

Name Hierarchy
A 4
B 3
C 1
D 2
E 5

Now, what i want is if the value is in tie(In our example the names C and D is in tie). So, i want to check the hierarchy in another table. If the hierarchy is Higher than the rank also should high. The expected ourput is

Name Value Rank
A 582 5
B 3534 4
C 16707 1
D 16707 2
E 6917 3

Can u please give me the solution

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-19 : 03:15:32

[code]declare @test table
(
Name char(1),
Value int, [Rank] int)
insert into @test (Name,Value)
select 'A', 582 union all
select 'B', 3534 union all
select 'C', 16707 union all
select 'D', 16707 union all
select 'E', 6917

declare @test1 table
(Name char(1),Hierarchy int)
insert into @test1
select 'A', 4 union all
select 'B', 3 union all
select 'C', 1 union all
select 'D', 2 union all
select 'E', 5

select t.Name,t.Value,rank() over(order by t.Value desc,t1.Hierarchy) as Rnk
FROm @test t
join @test1 t1
on t1.Name=t.Name
order by t.Name

output
---------------------
Name Value Rnk
---- ----------- ----
A 582 5
B 3534 4
C 16707 1
D 16707 2
E 6917 3[/code]

Govilakshmi
Starting Member

8 Posts

Posted - 2008-08-20 : 07:18:58

Thanks a lot visak,

Its working fine.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-20 : 07:43:09

quote:
Originally posted by Govilakshmi

Thanks a lot visak,

Its working fine.

you're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources