Puzzler-ranking

[gg]

Is there some way that Access can rank this? I have a
program that scores races. At the end of a series of
races, the overall score is determined by adding the
points for individual races. The lowest point total
wins. One of the tie breaking methods gives the overall
win to the person that beat the other in the last race.
If tied in the last race, then the next-to-last race and
so on until one is the winner. The number of races in a
series can be anything from 2 to 20 plus.
Sample:
Points in each Race: Point My Prgm. Desired
Racer: 1stRace 2nd 3rd 4th Total Rank Rank
Alpha 2 2 1 1 6 1 1
Bravo 1 1 2 2 6 1 2
Charlie 3 4 3 5 15 3 3
Delta 3 3 4 5 15 3 4
Echo 6 6 6 3 21 5 5
etc.

 

[Tim Ferguson]

The lowest point total
wins. One of the tie breaking methods gives the overall
win to the person that beat the other in the last race.

a) I am assuming you have some kind of grouping/ summing query with a sort
criterion. You should be able to join on the results for the last race and
add it as a second sorting criterion:-

ORDER BY Sum(Totals.RacePoints) DESC, LastRace.RacePoints DESC

b) the m.p.a.adpsqlserver newsgroups has some very high level SQL gurus
that can probably come up with some excellent solutions.

Best wishes


Tim F

 

[Peter Russell]

a) I am assuming you have some kind of grouping/ summing query with a
sort criterion. You should be able to join on the results for the last
race and add it as a second sorting criterion:-

ORDER BY Sum(Totals.RacePoints) DESC, LastRace.RacePoints DESC

This isn't really sufficient, Tim. (I think you may be saying that,
anyway).

I think you have to have as many OrderBy expressions as there are races
and even then you can't GUARANTEE there are no ties. (Some ties may be
resolved in LastRace - 1, some in LastRace - 2 etc.) You would still have
to test whether the final sequence contained ties.

I don't know whether there is a limit to the number of ordering
expressions you can use.
If so then it would be necessary to concatenate results to reduce the
number of orderby expressions required.(this might make it easier to test
for any remaining ties).

I think it is an interesting problem, but unfortunately don't have
sufficient time to try anything out.

Excel is probably a more natural contender for this type of problem ,I
would have thought.

Regards

Peter Russell

 

[gg]

I should have said that sort ordering was not the problem,
I need the rank to be displayed/printed

 

[Marshall Barton]

. . . to concatenate results . . .

I think Peter may be onto something here.

I have no idea how your data is stored, but if you can get
it into rows the way you described it, then you could sort
the rows with:

ORDER BY pointtotal DESC, 4th & 3rd & 2nd & 1st DESC

If there might be 10 or more people, you'd have to format
them:
. . . Format(4th,"00") & Format(3rd,"00") & . . .