Ranking Records

[Guest]

I ant to assign a ranking to records based on two criteria. Each record
within my DB has a Year and a Quality Scoring. I want to add a field with the
relative rank (counting from 1 upwards) for each recod based on relative
Qualtiy Scoring within the Year.

Can anyone help me with this?

 

[Marshall Barton]

I ant to assign a ranking to records based on two criteria. Each record
within my DB has a Year and a Quality Scoring. I want to add a field with the
relative rank (counting from 1 upwards) for each recod based on relative
Qualtiy Scoring within the Year.

SELECT *,
(SELECT Count(*)
FROM table As X
WHERE X.Year = table.Year
And X.Score <= table.Score) As Rank
FROM table

 

[Guest]

Marshall, thanks for the rapid response. I've tried this, and it works well.
One issue though, where I have multiple records with the same Quality Score
they all recieve the same ranking (OK), but the ranking is the higher number
rather than the lower number (not OK). Eg

Customer Year Score Ranking Desired Ranking
A 1999 10 1 1
B 1999 8 4 2
C 1999 8 4 2
D 1999 8 4 2
E 1999 5 5 5

Can you help me fix this?

 

[Marshall Barton]

Sorry, I should have guessed that that's what you wanted.

SELECT *,
(SELECT Count(*)
FROM table As X
WHERE X.Year = table.Year
And X.Score < table.Score) + 1 As Rank
FROM table

 

[Guest]

Thanks Marshall, that works fine.

Sorry, I should have guessed that that's what you wanted.

SELECT *,
(SELECT Count(*)
FROM table As X
WHERE X.Year = table.Year
And X.Score < table.Score) + 1 As Rank
FROM table
--
Marsh
MVP [MS Access]

Marshall, thanks for the rapid response. I've tried this, and it works well.
One issue though, where I have multiple records with the same Quality Score
they all recieve the same ranking (OK), but the ranking is the higher number
rather than the lower number (not OK). Eg

Customer Year Score Ranking Desired Ranking
A 1999 10 1 1
B 1999 8 4 2
C 1999 8 4 2
D 1999 8 4 2
E 1999 5 5 5

 

[rahmad]

Marshall, thanks for the rapid response. I've tried this, and it works well.
One issue though, where I have multiple records with the same Quality Score
they all recieve the same ranking (OK), but the ranking is the higher number
rather than the lower number (not OK). Eg

Customer Year Score Ranking Desired Ranking
A 1999 10 1 1
B 1999 8 4 2
C 1999 8 4 2
D 1999 8 4 2
E 1999 5 5 5

I'm sorry Mr.Marshall
Would you like to help.

Last time I'm trying this

SELECT Test.Customer, Test.Year, Test.Score, (SELECT Count(Test.Score) FROM
Test As X WHERE X.Year = Test.Year And X.Score <= Test.Score) AS Rank
FROM Test
GROUP BY Test.Customer, Test.Year, Test.Score
ORDER BY Test.Score DESC;

and return me

Customer Year Score Ranking Desired Ranking
A 1999 8 5 1
B 1999 6 4 2
C 1999 5 3 3
D 1999 5 3 3
E 1999 4 1 4

I want the biggest score number at the top rank.
Which word should be revised?
Can I sort the rank Ascending?
And how to return top 10 values of the score which is sorted
descendly.

 

[Marshall Barton]

SELECT Test.Customer, Test.Year, Test.Score, (SELECT Count(Test.Score) FROM
Test As X WHERE X.Year = Test.Year And X.Score <= Test.Score) AS Rank
FROM Test
GROUP BY Test.Customer, Test.Year, Test.Score
ORDER BY Test.Score DESC;

and return me

Customer Year Score Ranking Desired Ranking
A 1999 8 5 1
B 1999 6 4 2
C 1999 5 3 3
D 1999 5 3 3
E 1999 4 1 4

I want the biggest score number at the top rank.
Which word should be revised?
Can I sort the rank Ascending?
And how to return top 10 values of the score which is sorted
descendly.

Where did the GROUP BY come from? I can not see any reason
for using it and it adds complications you don't need.
Also, using Count(*) is more efficient than
Count(Test.Score), so you should have a good reason before
changing it.

Selecting the top 10 scores descending should be the same as
selecting the top 10 rank sorted ascending, so I think this
will do all the things you want except for the rank 4
instead of 5.

SELECT TOP 10
Test.Customer, Test.Year, Test.Score,
(SELECT Count(*)
FROM Test As X
WHERE X.Year = Test.Year
And X.Score > Test.Score) + 1 AS Rank
FROM Test
ORDER BY 4 ASC