Solved

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

Posted on 2009-07-10
17
946 Views
Last Modified: 2012-06-27
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.
0
Comment
Question by:shawnlehner
  • 7
  • 4
  • 3
  • +2
17 Comments
 
LVL 15

Expert Comment

by:jinal
ID: 24829022
try this query in SQL Server 2005 or 2008.

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



0
 
LVL 15

Accepted Solution

by:
jinal earned 125 total points
ID: 24829027
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
 
LVL 31

Assisted Solution

by:RiteshShah
RiteshShah earned 125 total points
ID: 24829054
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24829564
What if you have people with same score?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24829567
I mean how do you distinguish them to assign them a rank
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831116
>>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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24831820
<<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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24831883
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24831889
<<Here is your case 3) using RANK():>>
Not mine.  It's up to the questionner to say what he needs.  But thanks anyway. ;))
0
 
LVL 41

Expert Comment

by:pcelba
ID: 24832513
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24832820
>>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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24833807
<<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
 
LVL 41

Expert Comment

by:pcelba
ID: 24834689
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24834821
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24834937

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

Expert Comment

by:pcelba
ID: 24835342
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
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 24835512
<<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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now