Monday, May 21, 2012

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





No comments:

Post a Comment