Finding the top ten fastest persons

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello!

I have a table with a lots of results and I need to get these columns in my
query:

Athlete, Competition, Year, Distance, Time

Time is a DateTime field.

How can I get the ten fastest athletes (with all the other information) for
a given distance in a single query?

If I use SELECT DISTINCT TOP 10 ... I get the ten fastest times but some
athletes occur more than once so I would like to have DISTINCT on the
athletes only.
 
Hi,


SELECT TOP 10 *
FROM (SELECT Athlete, MAX(Distance) As MDistance
FROM myTable
GROUP BY Athlete)
ORDER BY MDistance DESC



Hoping it may help,
Vanderghast, Access MVP
 
Use two queries --
MiroMinTime --
SELECT Miro.Athlete, Min(Miro.Time) AS MinOfTime
FROM Miro
GROUP BY Miro.Athlete;

SELECT TOP 10 Miro.*
FROM Miro INNER JOIN MiroMinTime ON (Miro.Time = MiroMinTime.MinOfTime) AND
(Miro.Athlete = MiroMinTime.Athlete)
ORDER BY MiroMinTime.MinOfTime;
 
Thanks!

This solution works but I have to create a view (MiroMinTime) for each
distance. I'm accessing the data from .NET and I was hoping there would be a
solution where I could use one query to get the right dataset.
 
Thanks for your response!

Your solution only returns the athlete and distance. I also need to have the
competition and year in the query result.
 
Hi,



Add the extra fields you need in the innermost SELECT list.


SELECT TOP 10 *
FROM (SELECT Athlete, MAX(Distance) As MDistance, Competition, Year
FROM myTable
GROUP BY Athlete)
ORDER BY MDistance DESC



Hoping it may help,
Vanderghast, Access MVP
 
Hi!

That's the whole problem. If I add those extra fields I get an error. I have
to add them in the Group by clause as well to make it work, but then I'm back
on square one because I get duplicate athletes. One athlete can compete in
more than one competition over the same distance so neither DISTINCT nor
GROUP BY works if both those fields are in the query.
 
Miro said:
I have a table with a lots of results and I need to get these columns in my
query:

Athlete, Competition, Year, Distance, Time

Time is a DateTime field.

How can I get the ten fastest athletes (with all the other information) for
a given distance in a single query?

If I use SELECT DISTINCT TOP 10 ... I get the ten fastest times but some
athletes occur more than once so I would like to have DISTINCT on the
athletes only.
Click to expand...

PMFBI

I would probably "divide-and-conquer"
(change "yurtable" to name of your table)

qryFastestTimeAthDist

SELECT
y.Athlete,
y.Distance,
Min(y.[Time]) As FastestTime
FROM
yurtable AS y
GROUP BY
y.Athlete,
y.Distance;

qryAllFields

SELECT
t.Athlete,
t.Competition,
t.Year,
t.Distance,
q.FastestTime
FROM
yurtable AS t
INNER JOIN
qryFastestTimeAthDist AS q
ON
t.Athlete = q.Athlete
AND
t.Distance = q.Distance
AND
t.[Time] = q.FastestTime;

then run your TOP 10 on "qryAllFields"
for a specific Distance....

Apologies again for butting in...
 
sorry.. I now (after the fact) see that would fail
to meet what you wanted...

Gary Walter" erroneously said:
Miro said:
I have a table with a lots of results and I need to get these columns in my
query:

Athlete, Competition, Year, Distance, Time

Time is a DateTime field.

How can I get the ten fastest athletes (with all the other information) for
a given distance in a single query?

If I use SELECT DISTINCT TOP 10 ... I get the ten fastest times but some
athletes occur more than once so I would like to have DISTINCT on the
athletes only.
Click to expand...

PMFBI

I would probably "divide-and-conquer"
(change "yurtable" to name of your table)

qryFastestTimeAthDist

SELECT
y.Athlete,
y.Distance,
Min(y.[Time]) As FastestTime
FROM
yurtable AS y
GROUP BY
y.Athlete,
y.Distance;

qryAllFields

SELECT
t.Athlete,
t.Competition,
t.Year,
t.Distance,
q.FastestTime
FROM
yurtable AS t
INNER JOIN
qryFastestTimeAthDist AS q
ON
t.Athlete = q.Athlete
AND
t.Distance = q.Distance
AND
t.[Time] = q.FastestTime;

then run your TOP 10 on "qryAllFields"
for a specific Distance....

Apologies again for butting in...
Click to expand...
 
Unfortunatelly! But thanks anyway!

I've made a workaround where I remove the duplicate athelete-rows in the
DataSet in my ASPX-page, but it still would be interesting to see if this can
be solved in a single query.


Gary Walter said:
sorry.. I now (after the fact) see that would fail
to meet what you wanted...

Gary Walter" erroneously said:
Miro said:
I have a table with a lots of results and I need to get these columns in my
query:

Athlete, Competition, Year, Distance, Time

Time is a DateTime field.

How can I get the ten fastest athletes (with all the other information) for
a given distance in a single query?

If I use SELECT DISTINCT TOP 10 ... I get the ten fastest times but some
athletes occur more than once so I would like to have DISTINCT on the
athletes only.
Click to expand...

PMFBI

I would probably "divide-and-conquer"
(change "yurtable" to name of your table)

qryFastestTimeAthDist

SELECT
y.Athlete,
y.Distance,
Min(y.[Time]) As FastestTime
FROM
yurtable AS y
GROUP BY
y.Athlete,
y.Distance;

qryAllFields

SELECT
t.Athlete,
t.Competition,
t.Year,
t.Distance,
q.FastestTime
FROM
yurtable AS t
INNER JOIN
qryFastestTimeAthDist AS q
ON
t.Athlete = q.Athlete
AND
t.Distance = q.Distance
AND
t.[Time] = q.FastestTime;

then run your TOP 10 on "qryAllFields"
for a specific Distance....

Apologies again for butting in...
Click to expand...
Click to expand...
 
I don't know about "net/asp,"
but in Access, you could try changing
field names for Time and Year to non-reserved
field names like CompTime and CompYear
so don't need brackets, then....


SELECT TOP 10
t.Athlete,
t.Competition,
t.CompYear,
t.Distance,
q.FastestTime
FROM
yurtable AS t
INNER JOIN
[SELECT
y.Athlete,
y.Distance,
Min(y.CompTime) As FastestTime
FROM
yurtable AS y
GROUP BY
y.Athlete,
y.Distance]. AS q
ON
t.Athlete = q.Athlete
AND
t.Distance = q.Distance
AND
t.CompTime = q.FastestTime
WHERE
t.Distance = xxxx
ORDER BY
q.FastestTime;

assuming athlete does not have same
fastest time at different competitions...

Miro said:
Unfortunatelly! But thanks anyway!

I've made a workaround where I remove the duplicate athelete-rows in the
DataSet in my ASPX-page, but it still would be interesting to see if this can
be solved in a single query.


Gary Walter said:
sorry.. I now (after the fact) see that would fail
to meet what you wanted...

Gary Walter" erroneously said:
:
I have a table with a lots of results and I need to get these columns in
my
query:

Athlete, Competition, Year, Distance, Time

Time is a DateTime field.

How can I get the ten fastest athletes (with all the other information)
for
a given distance in a single query?

If I use SELECT DISTINCT TOP 10 ... I get the ten fastest times but some
athletes occur more than once so I would like to have DISTINCT on the
athletes only.

PMFBI

I would probably "divide-and-conquer"
(change "yurtable" to name of your table)

qryFastestTimeAthDist

SELECT
y.Athlete,
y.Distance,
Min(y.[Time]) As FastestTime
FROM
yurtable AS y
GROUP BY
y.Athlete,
y.Distance;

qryAllFields

SELECT
t.Athlete,
t.Competition,
t.Year,
t.Distance,
q.FastestTime
FROM
yurtable AS t
INNER JOIN
qryFastestTimeAthDist AS q
ON
t.Athlete = q.Athlete
AND
t.Distance = q.Distance
AND
t.[Time] = q.FastestTime;

then run your TOP 10 on "qryAllFields"
for a specific Distance....

Apologies again for butting in...
Click to expand...
Click to expand...
Click to expand...
 
Hi,


Sorry, I should have double checked:


SELECT TOP 10 *
FROM (SELECT a.Athlete, a.Distance, LAST(a.Competition), LAST(a.Year)
FROM myTable As a INNER JOIN myTable As b ON
a.Athlete=b.Athlete
GROUP BY Athlete, a.Distance
HAVING a.Distance = MAX(b.Distance))
ORDER BY Distance DESC



The inner most query is solution 3 presented in
http://www.mvps.org/access/queries/qry0020.htm, ie, returning the associated
field that correspond to an aggregate (here, MAX(distance), in the site, it
is MAX(borrowingTime)) for each member of a group (each athlete, in the
example, each book).



Hoping it may help,
Vanderghast, Access MVP
 
Hi,


And another solution would be to use the first query:

SELECT TOP 10 *
FROM (SELECT Athlete, MAX(Distance) As MDistance
FROM myTable
GROUP BY Athlete)
ORDER BY MDistance DESC


and to make another query implying it, and the original table, with an inner
join on the two fields.


Hoping it may help,
Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ranking with Best athletes done. Still a liiiitle problem :-) 6
Best athletes and not best scores. Problem with SQL command 2
Can you help me simplify this query, if possible?? 6
Write textbox value from report to an related query field 1
Top Ten Albums 10
male & female lists 6
MAX/MIN in range potantially including text 1
Form Design Issue 4

Back
Top