Dubs
asked on
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].Student
FROM dbo.[Score2000-01] INNER JOIN
dbo.[ScoreII2000-01] ON dbo.[Score2000-01].Score > dbo.[ScoreII2000-01].Score
GROUP BY dbo.[Score2000-01].Student
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
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].Student
FROM dbo.[Score2000-01] INNER JOIN
dbo.[ScoreII2000-01] ON dbo.[Score2000-01].Score > dbo.[ScoreII2000-01].Score
GROUP BY dbo.[Score2000-01].Student
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
Julianva
ranking is the count of what ??
ASKER
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
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
ASKER
wait a minute im lying- the equation works better (no joins) but is still spitting out rank 2 as the first ranking.
greg
greg
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Don't think the calc can work with an "=" in the comparison of scores.
ASKER
no worries- understood...makes sense now..
greg
greg