Ranking with Best athletes done. Still a liiiitle problem :-)

[Irene]

Hi all,

Well, I managed to get the list of the best TOP <n> athletes in a
certain competition type (in this example is "Triathlon").

(Thanks to Michel and his page:
http://www.mvps.org/access/queries/qry0020.htm)
----
Athletics database. Tables: Athletes, Competitions, Scores,
CompetitionTypes.

SELECT Athletename, P.Score, CompetitionDate, CompetitionPlace
FROM ((Scores AS P INNER JOIN [SELECT TOP 10 AthleteID,Max(Score) as
MP FROM Scores INNER JOIN Competitions ON
Competitions.CompID=Scores.CompID WHERE CompType='triathlon' GROUP BY
AthleteID]. AS Q ON (P.AthleteID=Q.AthleteID) AND (P.Score=Q.MP))
INNER JOIN Competitions ON Competitions.CompID=P.CompID) INNER JOIN
Athletes ON Athletes.AthleteID=P.AthleteID
ORDER BY P.Score DESC;
----

I have still a little problem when the same athlet reaches the same
score in two or more different competitions. In this case, all the
competitions are listed. This is not what I want, I would like to list
the first best result of each athlet, so to say if the same score has
been reached on 12.Aug.2002 and 23.Apr.2003, just the oldest date's
competition should be listed.

I have tried to modify the given query without success.

Ok, if you find a bit of time to help me many thanks, otherwise have a
nice weekend!

Irene

 

[HSalim]

Irene,
I'd like to help, but would have liked to have sample data. it helps

IT is not clear how your scoring system works - it appears as if it is like
an average of some kind
- 9,9.5,10, with a resulting score of 9.5
as a result, the one with the highest score is the winner(gold)

Question: What if one athlete won the gold in three triathlons but still
had different scores -
say 9.7 on 11 May 2001, 9.5 on 12.Aug.2002 and 9.6 on 23.Apr.2003
which competition do you want?

 

[Irene]

Irene,
I'd like to help, but would have liked to have sample data. it helps

Would you like me to send you a sample of the database?

IT is not clear how your scoring system works - it appears as if it is like
an average of some kind
- 9,9.5,10, with a resulting score of 9.5
as a result, the one with the highest score is the winner(gold)

No, no average. The best athlete is the one that reached the best
result in 1 competition in a competitiontype in a range of years you
can select.

Actually, my database is far more complicated of what I posted here.
In the example I posted, I consider the score as a simple integer: the
bigger the number the best the result. Consider it as a ranking for a
videogame context, same thing.

In real database I have 4 tables: 1 for scores measured in time
(minimum=best), 1 for scores measured in lenght(max=best), 1 for
points (=example)(max=best), 1 for positioning (1^, 2^, 3^)(min=best).
:-((

Question: What if one athlete won the gold in three triathlons but still
had different scores -
say 9.7 on 11 May 2001, 9.5 on 12.Aug.2002 and 9.6 on 23.Apr.2003
which competition do you want?

This list is for a sportive organization to check who were the best
athetes they have ever coached. So, it does not matter how many "gold"
this athlete won or if he did not won any, it just need to appear once
in the "best athletes" list.

His positioning in the list is relative to what the other athletes did
in the same competiontype in all the competitions the organization
participated in.
Answering your question I need just the max score: 9.7 on 11 May 2001.

*** My Problem: Following your example, if the same athete did 9.7 on
12 August 2002 and 9.7 on 23.Apr.2003 too, I see all the three results
and dates in a row from the oldest to the newest. On the contrary. I
just want to view the 9.7 on 11 May 2001.***

Of course I also have a "best results" list where I list the best
results for each competitiotype. That does not give me any problem.
Examples:

Best results in Triathlon:
Atle A 100 date
Atle B 97 date
Atle C 92 12.aug.2001
Atle C 92 13.aug.2002
Atle A 91 date
Atle B 90 11.apr.2001
Atle D 90 13.aug.2002
Atle A 86 date
Atle D 80 date
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003
(as you can see the order is by score,date)

Best athletes in Triathlon (must be):
Atle A 100
Atle B 97
Atle C 92 12.aug.2001
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(order by score and date as well but each athete appears just once!)

Best athletes in Triathlon (what I get now):
Atle A 100
Atle B 97
*Atle C 92 12.aug.2001
*Atle C 92 13.aug.2002
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(* when an athete reaches his best result twice or more, in the list
all the competition for that athelete are displayed. I just need the
first (the oldest) to appear!)

----
Athletics database. Tables: Athletes, Competitions, Scores,
CompetitionTypes.

SELECT Athletename, P.Score, CompetitionDate, CompetitionPlace
FROM ((Scores AS P INNER JOIN [SELECT TOP 10 AthleteID,Max(Score) as
MP FROM Scores INNER JOIN Competitions ON
Competitions.CompID=Scores.CompID WHERE CompType='triathlon' GROUP BY
AthleteID]. AS Q ON (P.AthleteID=Q.AthleteID) AND (P.Score=Q.MP))
INNER JOIN Competitions ON Competitions.CompID=P.CompID) INNER JOIN
Athletes ON Athletes.AthleteID=P.AthleteID
ORDER BY P.Score DESC;
----

 

[Tom Ellison]

I was interested in crafting a solution to your problem earlier, but
wasn't confident of the solution. If you send me a copy I could test
a solution and return it to you (assuming I'm successful). It must be
under 1 MB in size, so compress it and remove unnecessary tables,
forms, reports, etc. if necessary. Attach to an email. My address is
as shown - no funny business.

Irene,
I'd like to help, but would have liked to have sample data. it helps

Would you like me to send you a sample of the database?

IT is not clear how your scoring system works - it appears as if it is like
an average of some kind
- 9,9.5,10, with a resulting score of 9.5
as a result, the one with the highest score is the winner(gold)

No, no average. The best athlete is the one that reached the best
result in 1 competition in a competitiontype in a range of years you
can select.

Actually, my database is far more complicated of what I posted here.
In the example I posted, I consider the score as a simple integer: the
bigger the number the best the result. Consider it as a ranking for a
videogame context, same thing.

In real database I have 4 tables: 1 for scores measured in time
(minimum=best), 1 for scores measured in lenght(max=best), 1 for
points (=example)(max=best), 1 for positioning (1^, 2^, 3^)(min=best).
:-((

Question: What if one athlete won the gold in three triathlons but still
had different scores -
say 9.7 on 11 May 2001, 9.5 on 12.Aug.2002 and 9.6 on 23.Apr.2003
which competition do you want?

This list is for a sportive organization to check who were the best
athetes they have ever coached. So, it does not matter how many "gold"
this athlete won or if he did not won any, it just need to appear once
in the "best athletes" list.

His positioning in the list is relative to what the other athletes did
in the same competiontype in all the competitions the organization
participated in.
Answering your question I need just the max score: 9.7 on 11 May 2001.

*** My Problem: Following your example, if the same athete did 9.7 on
12 August 2002 and 9.7 on 23.Apr.2003 too, I see all the three results
and dates in a row from the oldest to the newest. On the contrary. I
just want to view the 9.7 on 11 May 2001.***

Of course I also have a "best results" list where I list the best
results for each competitiotype. That does not give me any problem.
Examples:

Best results in Triathlon:
Atle A 100 date
Atle B 97 date
Atle C 92 12.aug.2001
Atle C 92 13.aug.2002
Atle A 91 date
Atle B 90 11.apr.2001
Atle D 90 13.aug.2002
Atle A 86 date
Atle D 80 date
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003
(as you can see the order is by score,date)

Best athletes in Triathlon (must be):
Atle A 100
Atle B 97
Atle C 92 12.aug.2001
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(order by score and date as well but each athete appears just once!)

Best athletes in Triathlon (what I get now):
Atle A 100
Atle B 97
*Atle C 92 12.aug.2001
*Atle C 92 13.aug.2002
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(* when an athete reaches his best result twice or more, in the list
all the competition for that athelete are displayed. I just need the
first (the oldest) to appear!)

----
Athletics database. Tables: Athletes, Competitions, Scores,
CompetitionTypes.

SELECT Athletename, P.Score, CompetitionDate, CompetitionPlace
FROM ((Scores AS P INNER JOIN [SELECT TOP 10 AthleteID,Max(Score) as
MP FROM Scores INNER JOIN Competitions ON
Competitions.CompID=Scores.CompID WHERE CompType='triathlon' GROUP BY
AthleteID]. AS Q ON (P.AthleteID=Q.AthleteID) AND (P.Score=Q.MP))
INNER JOIN Competitions ON Competitions.CompID=P.CompID) INNER JOIN
Athletes ON Athletes.AthleteID=P.AthleteID
ORDER BY P.Score DESC;
----

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[HSalim]

Since Tom has so graciously offered to help you, i'll take on an observer
role.

HS

I was interested in crafting a solution to your problem earlier, but
wasn't confident of the solution. If you send me a copy I could test
a solution and return it to you (assuming I'm successful). It must be
under 1 MB in size, so compress it and remove unnecessary tables,
forms, reports, etc. if necessary. Attach to an email. My address is
as shown - no funny business.

"HSalim" <[email protected]> wrote in message

Irene,
I'd like to help, but would have liked to have sample data. it helps

Would you like me to send you a sample of the database?

IT is not clear how your scoring system works - it appears as if it is like
an average of some kind
- 9,9.5,10, with a resulting score of 9.5
as a result, the one with the highest score is the winner(gold)

No, no average. The best athlete is the one that reached the best
result in 1 competition in a competitiontype in a range of years you
can select.

Actually, my database is far more complicated of what I posted here.
In the example I posted, I consider the score as a simple integer: the
bigger the number the best the result. Consider it as a ranking for a
videogame context, same thing.

In real database I have 4 tables: 1 for scores measured in time
(minimum=best), 1 for scores measured in lenght(max=best), 1 for
points (=example)(max=best), 1 for positioning (1^, 2^, 3^)(min=best).
:-((

Question: What if one athlete won the gold in three triathlons but still
had different scores -
say 9.7 on 11 May 2001, 9.5 on 12.Aug.2002 and 9.6 on 23.Apr.2003
which competition do you want?

This list is for a sportive organization to check who were the best
athetes they have ever coached. So, it does not matter how many "gold"
this athlete won or if he did not won any, it just need to appear once
in the "best athletes" list.

His positioning in the list is relative to what the other athletes did
in the same competiontype in all the competitions the organization
participated in.
Answering your question I need just the max score: 9.7 on 11 May 2001.

*** My Problem: Following your example, if the same athete did 9.7 on
12 August 2002 and 9.7 on 23.Apr.2003 too, I see all the three results
and dates in a row from the oldest to the newest. On the contrary. I
just want to view the 9.7 on 11 May 2001.***

Of course I also have a "best results" list where I list the best
results for each competitiotype. That does not give me any problem.
Examples:

Best results in Triathlon:
Atle A 100 date
Atle B 97 date
Atle C 92 12.aug.2001
Atle C 92 13.aug.2002
Atle A 91 date
Atle B 90 11.apr.2001
Atle D 90 13.aug.2002
Atle A 86 date
Atle D 80 date
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003
(as you can see the order is by score,date)

Best athletes in Triathlon (must be):
Atle A 100
Atle B 97
Atle C 92 12.aug.2001
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(order by score and date as well but each athete appears just once!)

Best athletes in Triathlon (what I get now):
Atle A 100
Atle B 97
*Atle C 92 12.aug.2001
*Atle C 92 13.aug.2002
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(* when an athete reaches his best result twice or more, in the list
all the competition for that athelete are displayed. I just need the
first (the oldest) to appear!)

----
Athletics database. Tables: Athletes, Competitions, Scores,
CompetitionTypes.

SELECT Athletename, P.Score, CompetitionDate, CompetitionPlace
FROM ((Scores AS P INNER JOIN [SELECT TOP 10 AthleteID,Max(Score) as
MP FROM Scores INNER JOIN Competitions ON
Competitions.CompID=Scores.CompID WHERE CompType='triathlon' GROUP BY
AthleteID]. AS Q ON (P.AthleteID=Q.AthleteID) AND (P.Score=Q.MP))
INNER JOIN Competitions ON Competitions.CompID=P.CompID) INNER JOIN
Athletes ON Athletes.AthleteID=P.AthleteID
ORDER BY P.Score DESC;
----

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Tom Ellison]

Dear Irene:

A solution, or partial solution, has been prepared. I attempted to
send you an email with the database attached, but it bounced. I sent
just an email and it did not bounce.

Here is the query I have created and tested thus far:

SELECT A.AthleteID, C.CompetitionID, S.Score
FROM Competitions AS C
INNER JOIN (Athletes AS A
INNER JOIN Scores AS S ON A.AthleteID = S.AthleteID)
ON C.CompetitionID = S.CompetitionID
WHERE S.Score = (SELECT MAX(S1.Score) FROM Scores S1
WHERE S1.AthleteID = A.AthleteID)
AND C.CompetitionDate = (SELECT MIN(C1.CompetitionDate)
FROM Competitions C1
INNER JOIN Scores S1 ON S1.CompetitionID = C1.CompetitionID
WHERE S1.AthleteID = A.AthleteID
AND S1.Score = (SELECT MAX(S2.Score) FROM Scores S2
WHERE S2.AthleteID = S1.AthleteID));

This selects each athlete (by ID) and shows this top score. It shows
only the first competition (CompetitionID) in which he achieved that
score.

The second subquery has a subquery within it. That is a technique
I've rarely needed, so I'd say this is getting a bit complex, at least
for Jet. There is a trick in here as well. Normally, I'd want the
last line above to read:

WHERE S2.AthleteID = A.AthleteID

However, in Jet I have the understanding that, when nesting
subqueries, you can only reference one level above the one you are
writing. Fortunately, the intermediate level here contains:

WHERE S1.AthleteID = A.AthleteID

Because of this, we are able to reference AthleteID using the
intermediate level subquery column S1.AthleteID, which we are already
assured is the same value as A.AthleteID.

Sorry if the explanation seems a bit complex. Well, the situation is
a bit complex.

Please let me know if this has helped, and if there is more that needs
done. I really believe the above technique, if not the query itself,
is a bit step on the road to solving your need.

Irene,
I'd like to help, but would have liked to have sample data. it helps

Would you like me to send you a sample of the database?

IT is not clear how your scoring system works - it appears as if it is like
an average of some kind
- 9,9.5,10, with a resulting score of 9.5
as a result, the one with the highest score is the winner(gold)

No, no average. The best athlete is the one that reached the best
result in 1 competition in a competitiontype in a range of years you
can select.

Actually, my database is far more complicated of what I posted here.
In the example I posted, I consider the score as a simple integer: the
bigger the number the best the result. Consider it as a ranking for a
videogame context, same thing.

In real database I have 4 tables: 1 for scores measured in time
(minimum=best), 1 for scores measured in lenght(max=best), 1 for
points (=example)(max=best), 1 for positioning (1^, 2^, 3^)(min=best).
:-((

Question: What if one athlete won the gold in three triathlons but still
had different scores -
say 9.7 on 11 May 2001, 9.5 on 12.Aug.2002 and 9.6 on 23.Apr.2003
which competition do you want?

This list is for a sportive organization to check who were the best
athetes they have ever coached. So, it does not matter how many "gold"
this athlete won or if he did not won any, it just need to appear once
in the "best athletes" list.

His positioning in the list is relative to what the other athletes did
in the same competiontype in all the competitions the organization
participated in.
Answering your question I need just the max score: 9.7 on 11 May 2001.

*** My Problem: Following your example, if the same athete did 9.7 on
12 August 2002 and 9.7 on 23.Apr.2003 too, I see all the three results
and dates in a row from the oldest to the newest. On the contrary. I
just want to view the 9.7 on 11 May 2001.***

Of course I also have a "best results" list where I list the best
results for each competitiotype. That does not give me any problem.
Examples:

Best results in Triathlon:
Atle A 100 date
Atle B 97 date
Atle C 92 12.aug.2001
Atle C 92 13.aug.2002
Atle A 91 date
Atle B 90 11.apr.2001
Atle D 90 13.aug.2002
Atle A 86 date
Atle D 80 date
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003
(as you can see the order is by score,date)

Best athletes in Triathlon (must be):
Atle A 100
Atle B 97
Atle C 92 12.aug.2001
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(order by score and date as well but each athete appears just once!)

Best athletes in Triathlon (what I get now):
Atle A 100
Atle B 97
*Atle C 92 12.aug.2001
*Atle C 92 13.aug.2002
Atle D 90 13.aug.2002
Atle E 70 10.mar.2001
Atle F 70 20.apr.2003

(* when an athete reaches his best result twice or more, in the list
all the competition for that athelete are displayed. I just need the
first (the oldest) to appear!)

----
Athletics database. Tables: Athletes, Competitions, Scores,
CompetitionTypes.

SELECT Athletename, P.Score, CompetitionDate, CompetitionPlace
FROM ((Scores AS P INNER JOIN [SELECT TOP 10 AthleteID,Max(Score) as
MP FROM Scores INNER JOIN Competitions ON
Competitions.CompID=Scores.CompID WHERE CompType='triathlon' GROUP BY
AthleteID]. AS Q ON (P.AthleteID=Q.AthleteID) AND (P.Score=Q.MP))
INNER JOIN Competitions ON Competitions.CompID=P.CompID) INNER JOIN
Athletes ON Athletes.AthleteID=P.AthleteID
ORDER BY P.Score DESC;
----

Tom Ellison
Ellison Enterprises - Your One Stop IT Experts

 

[Irene]

Here is the query I have created and tested thus far:

SELECT A.AthleteID, C.CompetitionID, S.Score
FROM Competitions AS C
INNER JOIN (Athletes AS A
INNER JOIN Scores AS S ON A.AthleteID = S.AthleteID)
ON C.CompetitionID = S.CompetitionID
WHERE S.Score = (SELECT MAX(S1.Score) FROM Scores S1
WHERE S1.AthleteID = A.AthleteID)
AND C.CompetitionDate = (SELECT MIN(C1.CompetitionDate)
FROM Competitions C1
INNER JOIN Scores S1 ON S1.CompetitionID = C1.CompetitionID
WHERE S1.AthleteID = A.AthleteID
AND S1.Score = (SELECT MAX(S2.Score) FROM Scores S2
WHERE S2.AthleteID = S1.AthleteID));

Dear Tom,

Well, the one you gave me was related to the best score of an athelete
regardless the competition type (s)he participated in. BUt it was
wanted I believe.

With the added complication of the competitiontype and of the
ordering, it looks like this:

SELECT A.AthleteName, Score, CompetitionDate, CompetitionPlace, Notes
FROM Competitions AS C INNER JOIN (Athletes AS A INNER JOIN Scores AS
S ON A.AthleteID = S.AthleteID) ON C.CompetitionID = S.CompetitionID
WHERE S.Score = (SELECT MAX(S1.Score) FROM Scores S1 INNER JOIN
Competitions C2 ON C2.CompetitionID=S1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND CompetitionTypeID=@T) AND
C.CompetitionDate = (SELECT MIN(C1.CompetitionDate) FROM Competitions
C1 INNER JOIN Scores S1 ON S1.CompetitionID = C1.CompetitionID WHERE
S1.AthleteID = A.AthleteID AND S1.Score = (SELECT MAX(S2.Score) FROM
Scores S2 INNER JOIN Competitions C3 ON
C3.CompetitionID=S2.CompetitionID WHERE S2.AthleteID = S1.AthleteID
AND CompetitionTypeID=@T))
ORDER BY S.score DESC,competitiondate;

[ BTW, for the quickness of the SQL execution, is it better to put the
table name before the fieldname or not ? S.Score or just Score ? ]

Well, for the few competition types I have tested it with. It seems to
be working.

I hope to manage to solve my little(huge) problem with the
competitions measured in time too with this new query structure.

Should I have still problems, I dont think I can forget your email any
longer.

Thanks a lot for your time and availability.
Regards,
Irene

PS Sorry, I use hotmail for my posts in usenet only.