• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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
0
Dubs
Asked:
Dubs
  • 3
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now