locked
No rank for 0 or negative score but yet return row RRS feed

  • 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

    Friday, August 17, 2018 7:24 PM

All replies

  • 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

    Friday, August 17, 2018 7:45 PM
  • 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

    Monday, August 20, 2018 8:52 AM
  • User77042963 posted

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

    Monday, August 20, 2018 1:33 PM