SQL Query: Select record on ID and find "rank" based on order

try this query in SQL Server 2005 or 2008.

SELECT    ID , HighScore , Rank() over(Order By HighScore Desc) as 'Rank'
FROM         ScoreRank

What if you have people with same score?

I mean how do you distinguish them to assign them a rank

>>What if you have people with same score?<<
I believe that is the difference between RANK() and DENSE_RANK()

DECLARE @ScoreRank TABLE
    (
      ID INTEGER,
      HighScore INTEGER
    )

SET NOCOUNT ON
INSERT  @ScoreRank ( ID, HighScore )
VALUES  ( 1, 50 )
INSERT  @ScoreRank ( ID, HighScore )
VALUES  ( 2, 25 )
INSERT  @ScoreRank ( ID, HighScore )
VALUES  ( 3, 99 )
INSERT  @ScoreRank ( ID, HighScore )
VALUES  ( 4, 5 )
INSERT  @ScoreRank ( ID, HighScore )                -- Added this duplicate
VALUES  ( 5, 25 )

SELECT  ID,
        HighScore,
        RANK() OVER ( ORDER BY HighScore DESC ) 'Rank'
FROM    @ScoreRank

SELECT  ID,
        HighScore,
        DENSE_RANK() OVER ( ORDER BY HighScore DESC ) 'Rank'
FROM    @ScoreRank

Output:

ID          HighScore   Rank
----------- ----------- --------------------
3           99          1
1           50          2
2           25          3
5           25          3
4           5           5

ID          HighScore   Rank
----------- ----------- --------------------
3           99          1
1           50          2
2           25          3
5           25          3
4           5           4

<<I believe that is the difference between RANK() and DENSE_RANK()>>
Thanks Antony.  I am not very familiar with these functions.

I asked because there are in fact 3 possibilities to the problem.  The two you pointed out and the one that would consist of having separate ranks for equal values based on ID.  Something like

ID          HighScore   Rank
----------- ----------- --------------------
3           99          1
1           50          2
2           25          3
5           25          4
4           5           5

But without knowing what the questionners wants, it's hard to say...In any case, here is another approach which does not assume sorting...

1) In case one assumes that rank places all equal grades at same rank

select A.HighScore, count(distinct A.HighScore) as ranking from yourtable A
                inner join yourtable B
                          on A.HighScore > = B.HighScore and B.ID = @ID
group by A.HighScore

2) In case does *not* one assumes that rank places all equal grades at same rank

select A.HighScore, count(A.HighScore) as ranking from yourtable A
                inner join yourtable B
                          on A.HighScore > = B.HighScore and B.ID = @ID
group by HighScore

Here is your case 3) using RANK():

SELECT  ID,
        HighScore,
        RANK() OVER ( ORDER BY HighScore DESC, ID ) 'Rank'
FROM    @ScoreRank

Output:

ID          HighScore   Rank
----------- ----------- --------------------
3           99          1
1           50          2
2           25          3
5           25          4
4           5           5

<<Here is your case 3) using RANK():>>
Not mine.  It's up to the questionner to say what he needs.  But thanks anyway. ;))

I think shawnlehner already told what he needs:  Rank for given ID and DENSE_RANK is closer than anything else, so:

SELECT a.Rank
   FROM (SELECT  ID, HighScore, DENSE_RANK() OVER ( ORDER BY HighScore DESC ) Rank
                 FROM YourTable) a
 WHERE a.ID = @GivenID

I am sure he does not need gaps created by RANK() and he does not need ROW_NUMBER() because it is not the rank he described.

>>I think shawnlehner already told what he needs:  Rank for given ID and DENSE_RANK is closer than anything else, so:<<
Why don't we let the author decide, instead of jumping to conclusions as to what they want or don't want.  Incidentally your solution is virtually the same as the one here http:#a24829054

>>he does not need ROW_NUMBER() because it is not the rank he described.<<
No one has suggested they use that.  Ritesh only mentioned in passing that

<quote>
Rank
Dense_Rank
Row_Number

are powerful tools in SQL Server 2005+, have a look at it's example at:

http://jerrytech.blogspot.com/2008/03/sql-2005-tsql-rank-rownumber-and.html
</quote>

<<I think shawnlehner already told what he needs:  Rank for given ID and DENSE_RANK is closer than anything else>>
Can you point out on this thread where the questionner claimed that DENSE_RANK is what he needs?

Please read the thread carefully and you will find the following facts:

> The questionner did ask for ranking but he did *not* mention at any point what ranking he needed or what method would meet his/her needs.
> You reproposed te same solution that RiteshShah proposed in #a24829054.
> Assuming the fact that there may be many solutions to the problem, DENSE_RANK is only one way to achieve some of the possible solutions.  I have proposed an alternative assuming the  questionner would be on SQL 2000 platform.

<<I am sure he does not need gaps >>
That is an assumption on your part.  It is better to let the questionner express his own needs.

Regards...

So, I've been jumping in because no one expert showed how to obtain the rank based on given ID. You simply did not offer the accurate solution in 19 hours after the question was placed and it was waiting for my return from the pub :-))).

Both of you complainers were offering solution based on the answers from jinal and RiteshShah. Do you think it is more correct than my jump in?

The questioner could not ask neither for rank nor for dense_rank because he did not know about their existence. I just offered the one which is by my opinion the closest one to his needs.

I don't care about points, I just don't like incomplete solutions.

Don't be so upset, we are not in the war, keep relaxing... :-)))

acperkins: I like your complete solutions containing all sample data. BTW, the ROW_NUMBER was implicitly suggested in answer ID:24831820.

Racimo: Did you test the "solution" which should work on SQL 2000?

pcelba,

I am afraid I do not agree with any of your comments, they are plain wrong and misleading.  I also find your use of the adjective "complainers" to be unprofessional and insulting at the same time.  Perhaps English is not your first languange, so I would recommend you try and be a tad more careful with your choice of words in future.  It is always best to err on the side of caution when you are not on familiar ground.

In view of the above I see no further need to continue this discussion.

Good luck,
Anthony

<<So, I've been jumping in because *no one* expert showed how to obtain the rank based on given ID.>>
acperkins already mentionned that your solution is very similar as the solution proposed in comment 24829054.  I do agree with him that your suggestion is simply a repetition.

<<Both of you complainers were offering solution based on the answers from jinal and RiteshShah.>>
I assume the term *complainers* is directed at both acperkins and I.  

Your assertion is false because the method I proposed does not even *use* a partition function.  Which does make your claim that I'd have used Ritesh and jinal's solution totally false.

 <<Do you think it is more correct than my jump in?>>
I do not recall anybody claiming anything.   We simply pointed out that the questionner should see what's best for him.  What is so wrong about this?

<<The questioner could not ask neither for rank nor for dense_rank because he did not know about their existence. I just offered the one which is by my opinion the closest one to his needs.>>
Please read comment 24831820.

The question here is *not* whether DENSE_RANK is *the* solution but which ranking method ought to be used (appart from the syntax used).  We simply formulated that it is up to the questionner to make up his mind about this.


<<Don't be so upset, we are not in the *war*, keep relaxing... :-)))>>
.Quite frankly, I do not believe is upset here, appart from somebody calling his fellows experts *complainers* and using the term *war* to describe what may well simply be a simple misunderstanding.  


<<Racimo: Did you test the "solution" which should work on SQL 2000?>>
Well, no.   But since you asked for it, here it is.   I realize, thanks to you, that the group by clause was not necessary...

Using the sample data gracefully provided by *acperkins*

1      50
2      25
3      99
4      5
5      25

select  count(distinct A.HighScore) as ranking from @ScoreRank A
                inner join @ScoreRank B
                          on A.HighScore > = B.HighScore and B.ID = 4  --> test with rank ID 4

...produces rank 4 equivalent of DENSE_RANK

select  count(distinct A.HighScore) as ranking from @ScoreRank A
                inner join @ScoreRank B
                          on A.HighScore > = B.HighScore and B.ID = 4

...produces rank 5 equivalent of RANK function

Yes, I am upset sometimes... English is not my mother's language, I don't understand others sometimes, they don't understand me sometimes (or always?)... Sorry for unsuitable words. I am suggesting to use just SQL from now.

I agree to finish this discussion.

BTW, the last select is still not fixed. :-)

<<English is not my mother's language, I don't understand others sometimes, they don't understand me sometimes (or always?>>
English is not my native language as well, and I understand how trickier online exchange might be for a foreigner.  This is why it is very important to measure one's words and respect one another.

<<Sorry for unsuitable words. >>
I thank you for this.  Recognizing one's fault is easy for nobody.  Case closed.

<<BTW, the last select is still not fixed. :-)>>
Thank you for pointing this out...Was a hasty copy and paste.  The last statement should in fact read

select  count(A.HighScore) as ranking from @ScoreRank A
                inner join @ScoreRank B
                          on A.HighScore > = B.HighScore and B.ID = 4

using count instead of count(distinct)