CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
user1447679
user1447679

Reputation: 3260

In SQL, I need to generate a ranking (1st, 2nd, 3rd) column, getting stuck on "ties"

I have a query that calculates points based on multiple criteria, and then orders the result set based on those points. 

SELECT * FROM (
    SELECT
         dbo.afunctionthatcalculates(Something, Something) AS Points1
        ,dbo.anotherone(Something, Something) AS Points2
        ,dbo.anotherone(Something, Something) AS Points3
        ,[TotalPoints] = dbo.function(something) + dbo.function(something) 
) AS MyData
ORDER BY MyData.TotalPoints

So my first stab at adding placement, rankings.. was this:

SELECT ROW_NUMBER() OVER(MyData.TotalPoints) AS Ranking, * FROM (
    SELECT same as above
) AS MyData
ORDER BY MyData.TotalPoints

This adds the Rankings column, but doesn't work when the points are tied.

Rank  |  TotalPoints
--------------------
1        100
2        90
3        90
4        80

Should be:

Rank  |  TotalPoints
--------------------
1        100
2         90
2         90
3         80

Not really sure about how to resolve this.

Thank you for your help.

Upvotes: 2

Views: 422

Answers (2)

RyanB
RyanB

Reputation: 757

DENSE_RANK() instead of ROW_NUMBER()

Upvotes: 1

NickyvV
NickyvV

Reputation: 1744

You should use the DENSE_RANK() function which takes the ties into account, as described here: http://msdn.microsoft.com/en-us/library/ms173825.aspx

Upvotes: 3

Related Questions