CODEWITHSUNDEEP
phpmysql
Rocket Ronnie
Rocket Ronnie

Reputation: 1116

mysql SUM until value is reached

I have made a website for a 'walking challenge', which has a table that logs miles walked.

The target is 2105 miles (Newcastle, UK to Istanbul).

On the home page i have a leaderboard which currently shows the 5 teams who have racked up the most miles.

I am using the following query to achive this:

SELECT 
    SUM(log.distance) AS l, 
    log.*, 
    team.*
FROM 
    team
RIGHT JOIN 
    log ON team.teamname = log.teamname                             
GROUP BY 
    log.teamname
ORDER BY 
    l DESC

However i want this leaderboard to show the 5 teams that finished first rather than who have walked the furthest. ie, the teams who reached 2105 miles first.

The current website can be viewed here

Upvotes: 0

Views: 551

Answers (2)

Jules Colle
Jules Colle

Reputation: 11939

Since you have a timestamp field that gets the current time everytime a team enters the number of miles it has walked, you could do domething like this:

    SELECT 
        SUM(log.distance) AS l,
        MAX(log.timestamp) AS t,
        log.*, 
        team.*
    FROM 
        team
    RIGHT JOIN 
        log ON team.teamname = log.teamname                          
    GROUP BY 
        log.teamname
    WHERE
        l >= 2105
    ORDER BY 
        t ASC

Keep in mind that this will only work if you don't allow a team to add extra miles after completing the target distance. If they are able to add extra miles after completing the target, let me know, and i'll try looking for another query

Upvotes: 0

Chuck Callebs
Chuck Callebs

Reputation: 16441

Add a nullable completedDate field to the table and populate it whenever someone completes the race. Order by the completed date.

There'd be no way to order by who finished first otherwise.

Upvotes: 3

Related Questions