CODEWITHSUNDEEP
sqlms-accessms-access-2010
clangers
clangers

Reputation: 586

GROUP BY and aggregate function query

I am looking at making a simple leader board for a time trial. A member may perform many time trials, but I only want for their fastest result to be displayed. My table columns are as follows:

Members    { ID (PK), Forename, Surname }
TimeTrials { ID (PK), MemberID, Date, Time, Distance }

An example dataset would be:

Forename | Surname | Date     | Time | Distance
Bill       Smith     01-01-11   1.14   100
Dave       Jones     04-09-11   2.33   100
Bill       Smith     02-03-11   1.1    100

My resulting answer from the example above would be:

Forename | Surname | Date     | Time | Distance
Bill       Smith     02-03-11   1.1    100
Dave       Jones     04-09-11   2.33   100

I have this so far, but access complains that I am not using Date as part of an aggregate function:

SELECT Members.Forename, Members.Surname, Min(TimeTrials.Time) AS MinOfTime, TimeTrials.Date
FROM Members
INNER JOIN TimeTrials ON Members.ID = TimeTrials.Member
GROUP BY Members.Forename, Members.Surname, TimeTrials.Distance
HAVING TimeTrials.Distance = 100
ORDER BY MIN(TimeTrials.Time);

IF I remove the Date from the SELECT the query works (without the date). I have tried using FIRST upon the TimeTrials.Date, but that will return the first date which is normally incorrect.

Obviously putting the Date as part of the GROUP BY would not return the result set that I am after.

Upvotes: 1

Views: 240

Answers (1)

HansUp
HansUp

Reputation: 97131

Make this task easier on yourself by starting with a smaller piece of the problem. First get the minimum Time from TimeTrials for each combination of MemberID and Distance.

SELECT 
    tt.MemberID,
    tt.Distance,
    Min(tt.Time) AS MinOfTime
FROM TimeTrials AS tt
GROUP BY
    tt.MemberID,
    tt.Distance;

Assuming that SQL is correct, use it in a subquery which you join back to TimeTrials again.

SELECT tt2.*
FROM
    TimeTrials AS tt2
    INNER JOIN
    (
        SELECT 
            tt.MemberID,
            tt.Distance,
            Min(tt.Time) AS MinOfTime
        FROM TimeTrials AS tt
        GROUP BY
            tt.MemberID,
            tt.Distance
    ) AS sub
    ON
            tt2.MemberID = sub.MemberID
        AND tt2.Distance = sub.Distance
        AND tt2.Time = sub.MinOfTime
WHERE tt2.Distance = 100
ORDER BY tt2.Time;

Finally, you can join that query to Members to get Forename and Surname. Your question shows you already know how to do that, so I'll leave it for you. :-)

Upvotes: 1

Related Questions