CODEWITHSUNDEEP
sql-server-2005functionrank
martin.softpro
martin.softpro

Reputation: 443

SQL Server 2005 RANK function

I have a problem in SQL Server 2005:

Suppose I have the table PLAYER (string player, int score, bool Active) and the this query :

SELECT PLAYER.player AS NAME,
   PLAYER.score AS SCORE,
   POSITION = CASE WHEN PLAYER.Active THEN RANK()OVER(ORDER BY score desc) else NULL end
from PLAYER

The problem is that when the player is not active, the positions generated are not consecutive.

For example :

JOHN,10000,1
PETER,5000,NULL (NOT ACTIVE)
CHARLES,2500,3 (SHOULD HAVE POSITION 2, NOT 3)

Sorry for my terrible English, I hope I have explained my point

Upvotes: 0

Views: 527

Answers (3)

Amogh Rai
Amogh Rai

Reputation: 159

Try this: 

SELECT PLAYER.player AS NAME, 
PLAYER.score AS SCORE, 
POSITION = RANK()OVER(ORDER BY score desc) 
from PLAYER
WHERE ACTIVE IS NOT NULL
UNION
SELECT PLAYER.player AS NAME, 
PLAYER.score AS SCORE,NULL  
from PLAYER
WHERE ACTIVE IS NULL

I am not sure how efficient this will be when compared to the already answered query but it is a little easier to understand.

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

declare @Player table
(
  player varchar(20),
  score int,
  state int
)

insert into @Player values ('JOHN', 10000, 1)
insert into @Player values ('PETER', 5000, NULL)
insert into @Player values ('PAUL', 5000, 2)
insert into @Player values ('CHARLES', 2500, 1)

select player as Name,
       score,
       case
          when state = 1 then
            rank() over(partition by state order by score desc)
          else null
       end as position
from @Player

Result:

Name                 score       position
-------------------- ----------- --------------------
PETER                5000        NULL
JOHN                 10000       1
CHARLES              2500        2
PAUL                 5000        NULL

Upvotes: 2

Tim Schmelter
Tim Schmelter

Reputation: 460048

Try this:

POSITION = 
   RANK()OVER(ORDER BY CASE WHEN Active IS NULL THEN 1 ELSE 0 END ASC, score DESC)

Edit:

...but the problem is that actually my "Active" column is not boolean, the real column is an integer called "state" and it must equal 1 to be active. How should the code be in that case?

Then this should work:

POSITION = 
   RANK()OVER(ORDER BY CASE WHEN State = 1 THEN 0 ELSE 1 END ASC, score DESC)

Upvotes: 1

Related Questions