Ranking by Subquery in Access

[Tiddles]

Hi

I have written a query in access to rank my scores, but I would like to rank these scores within a subset of all the scores. Here is my example

Class_ID Dog_Handler_ID Club Score Rank
1 12 Wallingford 32 4
1 13 Andover 95 8
1 14 Wallingford 40 5
2 12 Wallingford 93 7
2 14 Wallingford 21 3
3 12 Wallingford 92 6
3 13 Andover 14 2
3 14 Wallingford 14 2

What I would like is this

Class_ID Dog_Handler_ID Club Score Rank
1 12 Wallingford 32 1
1 13 Andover 95 3
1 14 Wallingford 40 2
2 12 Wallingford 93 2
2 14 Wallingford 21 1
3 12 Wallingford 92 3
3 13 Andover 14 1
3 14 Wallingford 14 1


If I could order by class_ID and then Rank that would be fab too.

My sql so far is

SELECT Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, (Select Count(*) from Q_Results Where [Score] < [Res1].[Score]+1;) AS Rank
FROM Q_Results AS Res1;

Many thanks

[hedges98]

Wouldn't this work?

SELECT Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, (Select Count(*) from Q_Results Where [Score] < [Res1].[Score]+1;) AS Rank
FROM Q_Results AS Res1
ORDER BY Res1.Class_ID, Rank;
 

[Tiddles]

no - when I use that query and run it I get a dialogue box asking to Enter Parameter Value Rank

[hedges98]

Can you post your SQL for Q_Results please?

EDIT: Also, how come the Rank field of your example queries has different values in each query?

EDIT AGAIN: I think I follow, if the lowest score in each class is ranked highest?

[Tiddles]

SELECT Results.Class_ID, Results.Dog_Handler_ID, Handlers.Club, Results.Time, IIF([Results].[Faults]>0,[Results].[Faults],0) AS Faults, Results.Eliminated, Results.Obstacle, IIf([Eliminated]=Yes,100-[obstacle],[time]+[faults]) AS Score
FROM Handlers INNER JOIN (Dog_Handler INNER JOIN Results ON Dog_Handler.Dog_Handler_ID = Results.Dog_Handler_ID) ON Handlers.Handler_ID = Dog_Handler.Handler_ID
GROUP BY Results.Class_ID, Results.Dog_Handler_ID, Handlers.Club, Results.Time, Results.Faults, Results.Eliminated, Results.Obstacle, IIf([Eliminated]=Yes,100-[obstacle],[time]+[faults]);

[hedges98]

I think I've confused myself. Can you explain this bit please
(Select Count(*) from Q_Results Where [Score] < [Res1].[Score]+1;)

Won't [Res1].[Score] be the same as [Score]?

EDIT: I just massively confused myself by trying to do two things at once so I'm going to leave this to someone who clearly knows what they are doing, ha!

[Tiddles]

Umm I am not sure - I have confused myself too as I have been trying things from various threads to get this to work

Thanks you for your help!

[hedges98]

I don't feel quite so silly now, ha!

Does your query return anything at the moment? Or does it ask for a parameter?

[Tiddles]

My query ran and returned the first table in the first post until I added the following to the end of the query:

ORDER BY Res1.Class_ID, Rank;

Then it asked for the parameter value.

Before I opened this post I was trying to use the solution in the following thread, but couldn't work it out - http://bytes.com/topic/access/answer...g-subset-query.

[hedges98]

SELECT Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score, COUNT(*) as Rank
FROM (
SELECT Class_ID, Dog_Handler_ID, Club, Score
FROM Q_Results
GROUP BY Class_ID, Dog_Handler_ID, Club, Score) AS Res1
INNER JOIN
(
SELECT Class_ID, Dog_Handler_ID, Club, Score
FROM Q_Results
GROUP BY Class_ID, Dog_Handler_ID, Club, Score
) AS b on Res1.Class_ID = b.Class_ID and Res1.Score >= b.Score
GROUP BY Res1.Class_ID, Res1.Dog_Handler_ID, Res1.Club, Res1.Score
ORDER BY Res1.Class_ID, Res1.Score, COUNT(*)

That should work but like the previous thread states, if two scores are the same then they will both be ranked 2 as opposed to 1.

Someone else might be able to offer a tidier solution though

[Tiddles]

Yay! that did it

Thank you sooooo much

[hedges98]

Awesome! No worries pal, I'm glad I've given something back (even if the legwork was already done for me!) after all the help I've received on here myself