List of best athlets and not best scores. Problem with SQL command

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 Athlets, the Competitions and the Scores
tables.

When I want to list of the best scores/ranking, I just do:

SELECT TOP <how-many-best> AthletsName, Competitiondate,
CompetitionPlace, Score
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
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 athlets, so to
say listing just the best results for each Athlet.

If I add a group by Athletsname 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:
---
SELECT TOP <how-many-best> AthletsName, Min(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
GROUP BY AthletsName
ORDER BY Min(Score)

for scores in time

SELECT TOP <how-many-best> AthletsName, Max(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
GROUP BY AthletsName
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 Athlets and not all the
best scores?

Many thanks for your replies.

Best regards,
Irene

Nov 12 '05 #1

TC

Questions like this are fundamentally dependent on what is the primary key
of each table.

You need to say what is the primary key of each table.

TC
Irene <it************@hotmail.com> wrote in message
news:cc**************************@posting.google.c om...

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 Athlets, the Competitions and the Scores
tables.

When I want to list of the best scores/ranking, I just do:

SELECT TOP <how-many-best> AthletsName, Competitiondate,
CompetitionPlace, Score
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
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 athlets, so to
say listing just the best results for each Athlet.

If I add a group by Athletsname 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:
---
SELECT TOP <how-many-best> AthletsName, Min(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
GROUP BY AthletsName
ORDER BY Min(Score)

for scores in time

SELECT TOP <how-many-best> AthletsName, Max(Score)
FROM Scores INNER JOIN Competitions ... INNER JOIN Athlets ...
GROUP BY AthletsName
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 Athlets and not all the
best scores?

Many thanks for your replies.

Best regards,
Irene

Nov 12 '05 #2

Irene

"TC" <a@b.c.d> wrote in message news:1064729294.712637@teuthos

Questions like this are fundamentally dependent on what is the primary key
of each table.

You need to say what is the primary key of each table.

TC

Glad to know.
Athletes - PK AthleteID
Competitions - PK CompetitionID
SCORES - no PK. FK CompetionID,AthleteID.

SELECT TOP <how-many-best> AthletsName, Min(Score)
FROM Scores INNER JOIN Competitions
ON Competitions.CompetitionID=Scores.CompetitionID INNER JOIN
Athlets ON Athletes.AthleteID=Scores.AthleteID
GROUP BY AthletsName
ORDER BY Min(Score)

Thanks
Irene

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Nov 12 '05 #3

TC

Ok. I say there is a problem with SCORES.

Please tell me the fields in that table.

TC
Irene <it************@hotmail.com> wrote in message
news:f4*************************************@mygat e.mailgate.org...

"TC" <a@b.c.d> wrote in message news:1064729294.712637@teuthos

Questions like this are fundamentally dependent on what is the primary key of each table.

You need to say what is the primary key of each table.

TC

Glad to know.
Athletes - PK AthleteID
Competitions - PK CompetitionID
SCORES - no PK. FK CompetionID,AthleteID.

SELECT TOP <how-many-best> AthletsName, Min(Score)
FROM Scores INNER JOIN Competitions
ON Competitions.CompetitionID=Scores.CompetitionID INNER JOIN
Athlets ON Athletes.AthleteID=Scores.AthleteID
GROUP BY AthletsName
ORDER BY Min(Score)

Thanks
Irene

--
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Nov 12 '05 #4