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

Here is the situation; I have a table which contains the following columns: ID, Highscore. What I need to do is figure out what rank a highscore is based on an ID provided... For example if I have the following data:

ID    Highscore
1     50
2     25
3     99
4     5

And I wanted to find the rank for ID 3 the rank returned would be 1 because it is the highest score. If I wanted to find the rank for ID 2 then a rank of 3 would be returned because it is the third highest score. I have done quite a bit of searching but I fear I do not have the correct search terms so it is being difficult ;)

Thanks in advance for any help you have to offer.
LVL 4
shawnlehnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jinalCommented:
try this query in SQL Server 2005 or 2008.

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



0
jinalCommented:
For finding perticular rank

Select * from(SELECT    ID , HighScore , Rank() over(Order By HighScore Desc) as 'Rank'
FROM         ScoreRank) as tab
Where rank  = 1
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RiteshShahCommented:
you can use dense_rank also in your situation. have a look

Select * from(SELECT    ID , HighScore , dense_Rank() over(Order By HighScore Desc) as 'Rank'
FROM         ScoreRank) as tab
Where rank  = 1

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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
What if you have people with same score?
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
I mean how do you distinguish them to assign them a rank
0
Anthony PerkinsCommented:
>>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
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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
0
Anthony PerkinsCommented:
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
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<Here is your case 3) using RANK():>>
Not mine.  It's up to the questionner to say what he needs.  But thanks anyway. ;))
0
pcelbaCommented:
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.
0
Anthony PerkinsCommented:
>>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>
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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...
0
pcelbaCommented:
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?
0
Anthony PerkinsCommented:
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
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:

<<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


0
pcelbaCommented:
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. :-)
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.