I
Irene
Hi all,
I have set up a simple VB program (and later on an ASP interface) to
manage an Athletics database. I'm using Access 2000.
To simplify, I have the Athletes, the Competitions and the Scores
tables.
When I want to list of the best scores/ranking, I just do:
[1]SELECT TOP <how-many-best> AthleteName, Competitiondate,
CompetitionPlace, Score
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
ORDER BY Score [DESC]
([DESC] is present if the scores are measured in times and not if they
are lengths)
I come into a problem whehter I want to list the best athletes, so to
say listing just the best results for each Athlete.
If I add a group by Athletename parameter in the SQL, I am not any
longer able to display Competitiondate, CompetitionPlace because they
are not part of the aggregate function. :-(
So to say, I can only do:
---
[2a]SELECT TOP <how-many-best> AthleteName, Min(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
GROUP BY AthleteName
ORDER BY Min(Score)
for scores in time
[2b]SELECT TOP <how-many-best> AthleteName, Max(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
GROUP BY AthleteName
ORDER BY Max(Score)
for scores in lenght
---
How can I do, which SQL command can I use to be able to view all the
fields of the first SQL with just the best Athletes and not all the
best scores?
Many thanks for your replies.
Best regards,
Irene
I have set up a simple VB program (and later on an ASP interface) to
manage an Athletics database. I'm using Access 2000.
To simplify, I have the Athletes, the Competitions and the Scores
tables.
When I want to list of the best scores/ranking, I just do:
[1]SELECT TOP <how-many-best> AthleteName, Competitiondate,
CompetitionPlace, Score
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
ORDER BY Score [DESC]
([DESC] is present if the scores are measured in times and not if they
are lengths)
I come into a problem whehter I want to list the best athletes, so to
say listing just the best results for each Athlete.
If I add a group by Athletename parameter in the SQL, I am not any
longer able to display Competitiondate, CompetitionPlace because they
are not part of the aggregate function. :-(
So to say, I can only do:
---
[2a]SELECT TOP <how-many-best> AthleteName, Min(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
GROUP BY AthleteName
ORDER BY Min(Score)
for scores in time
[2b]SELECT TOP <how-many-best> AthleteName, Max(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athletes ...
GROUP BY AthleteName
ORDER BY Max(Score)
for scores in lenght
---
How can I do, which SQL command can I use to be able to view all the
fields of the first SQL with just the best Athletes and not all the
best scores?
Many thanks for your replies.
Best regards,
Irene