Re: Ranking Scores in a Query
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Tue, 18 Dec 2007 16:54:54 -0500
Here is a solution with a join:
SELECT a.fee, a.key, LAST(a.score), COUNT(*) AS rank
FROM yourQuery AS a INNER JOIN yourQuery AS b
ON a.fee = b.fee
AND a.key =b.key
AND a.score <= b.score
GROUP BY a.fee, a.key
If there are ex-equo, they all got the higest rank: 10, 20, 20, 30 will be
respectively ranked 1, 3, 3, 4
SELECT a.fee, a.key, LAST(a.score), 1+ COUNT(*) AS rank
FROM yourQuery AS a LEFT JOIN yourQuery AS b
ON a.fee = b.fee
AND a.key =b.key
AND a.score < b.score
GROUP BY a.fee, a.key
will return 1, 2, 2, 4
Hoping it may help,
Vanderghast, Access MVP
"Steve S" <SteveS@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF15BBA2-FFE9-401E-98C9-2EB59204F55A@xxxxxxxxxxxxxxxx
I have a query that uses 3 tables and 3 Xtab queries to use as record
source
for a report. Three of the numeric fields created as output from this
query
are:
Fee Key Score
876 10 236.8
876 10 287.0
876 10 123.7
876 13 231.9
876 13 321.5
I need to add a 'Rank' field to produce:
Fee Key Score Rank
876 10 236.8 2
876 10 287.0 1
876 10 123.7 3
876 13 231.9 2
876 13 321.5 1
The final result will be that the highest score, within Key, within Fee is
First and so forth. Is this possible to do with a query. I have other
ranking routines where the values being ranked are in a table but I would
like to do this in a query if possible. I just can't seem to get it to
work.
Help is appreciated
.
- Prev by Date: Re: Remove Specific Text/Character in Specific Place
- Next by Date: Re: Select Query Question Continued
- Previous by thread: RE: Ranking Scores in a Query
- Next by thread: RE: Ranking Scores in a Query
- Index(es):
Relevant Pages
|
