Ranking

R

Richardson

I have a query with a member listing. They have all previously been
assigned scores which are integer values ranging from 1-1200 but they are
not sesquential and there is not pattern to how they are spaced in value.
Field name Score
Is there a formula I can use to have the query sort the member ascending by
score and then add a new column that is the rank. Rank would need to be
assign as 1,2,3,etc with no spaces and all integer values?
I really appreciate any help you can offer.

Lori
 
M

Michel Walsh

Hi,



SELECT a.f1, LAST(a.f2), LAST(a.f3), COUNT(*) As Rank
FROM myTable As a INNER JOIN myTable As b
ON a.score >= b.score
GROUP BY a.f1



where f1 is probably the primary key, f2, f3, .. are all the other fields
you wish to see.


Hoping it may help,
Vanderghast, Access MVP
 
M

Michel Walsh

Hi,


... use :

ON a.score <= b.score


to get the reverse ordering... evidently.



Vanderghast, Access MVP
 
T

Tonín

Following, I've pasted an answer in microsoft.public.access today that could
meet what you are looking for:

Hope being helpful

Tonín



===========================
 
Top