By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,635 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,635 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
"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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.