Finding the top ten fastest persons

[Miro]

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.

 

[Michel Walsh]

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

 

[KARL DEWEY]

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;

 

[Miro]

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.

 

[Miro]

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.

 

[Michel Walsh]

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

 

[Miro]

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.

 

[Gary Walter]

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...

 

[Gary Walter]

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

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...

 

[Miro]

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.

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

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...

 

[Gary Walter]

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...

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.

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

:
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...

 

[Michel Walsh]

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

 

[Michel Walsh]

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