No rank for 0 or negative score but yet return row

Question

User517361227 posted

Hello,

I have idea about how to use rank function but what I am trying to achieve is that return all results from table but rank column should be null if score for row is 0 or negative.

Please tell me how to do that in SQL server 2012 and up?

Many thanks

Answer 1

User517361227 posted

I tried this with case statement and it is returning desired result, but still need to confirm if that is good approach or there is any other better way to do it

SELECT  
  Id,  
  Name,  
  Score,  
  (CASE WHEN Score > 0 THEN RANK() OVER (ORDER BY Score DESC) END) AS Position
 FROM Result

This returns Position as Null for score 0 or negative, but still is there any better approach to do the task or above is fine and does not hurt performance of query?

Thanks

Answer 2

User347430248 posted

Hi vinod_pravir...,

I had made a test with your query and find that you had develop a proper query and you can certainly use it.

It is simple and i don't think there is other query simple like this which generates same output.

Regards

Deepak

Answer 3

User77042963 posted

You can UNION ALL two query parts, one with ranking and the other without. This is just another option.