Sql rank equation starting with rank 2 rather than rank 1 (first place)

Hi all,

ive been struggling to get my rankings working in sql- it failed in access due to it not accepting my alias names although the northwind example was copied exactly so am using what i had in sql:


SELECT     TOP 100 PERCENT dbo.[Score2000-01].StudentId, dbo.[Score2000-01].Score AS Score, COUNT(*) AS GCSE_Ranking
FROM         dbo.[Score2000-01] INNER JOIN
                      dbo.[ScoreII2000-01] ON dbo.[Score2000-01].Score > dbo.[ScoreII2000-01].Score
GROUP BY dbo.[Score2000-01].StudentId, dbo.[Score2000-01].Score
ORDER BY dbo.[Score2000-01].Score

and with it im getting the ranks spit out but starting with 2 (2nd place) rather than 1- 7 students have the same score so therefore should come joint first, but the equation spits out this:

StudentId      Score      GCSE_RANKING
1025      11      2
1384      11      2
847      11      2
856      11      2
887      11      2
1022      12      7

Any ideas to make this work? ive already tried doing this via a create table command using an autonumber to autorank the entries but it wont handle mulitples of the same result, i tried a similar equation that accounted for this but i get some sort of invalid column name error i cannot crack so am reverting back to this way.

greg
DubsAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Don't think that can work you want an "=" in the computation.  I would think this would be what you need:

SELECT s.StudentId, s.Score AS Score,  
    (Select Count(*) from dbo.[Score2000-01] Where score <  s.Score) + 1 AS  GCSE_Rank
FROM dbo.[Score2000-01] AS S
ORDER BY S.Score Desc;


0
 
JulianvaCommented:
ranking is the count of what ??




0
 
DubsAuthor Commented:
of the number of student ids with the same score so the first five with the same score should be rank 1, ive since tried this and believe it to work:

SELECT s.StudentId, s.Score AS Score,  (Select Count(*) from dbo.[Score2000-01]  Where score >=  s.Score;) AS  GCSE_Rank
FROM dbo.[Score2000-01] AS S
ORDER BY S.Score Desc;

greg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DubsAuthor Commented:
wait a minute im lying- the equation works better (no joins) but is still spitting out rank 2 as the first ranking.

greg
0
 
Scott PletcherSenior DBACommented:
Correction to my first line of comment above (which, sorry, makes *no sense* at all):

Don't think the calc can work with an "=" in the comparison of scores.
0
 
DubsAuthor Commented:
no worries- understood...makes sense now..

greg
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.