CODEWITHSUNDEEP
mysql
Jon
Jon

Reputation: 91

Select results from specified year

I want the below query to only select results from a particular year. The eventID relates to an events table within which the event date is located.

championshipLeaderboard Table 

 ------------------------------------
 | ID | eventID | playerId | points |
 ------------------------------------

events Table

 ------------------
 | eventID | date |
 ------------------

I'm having trouble on where to place the inner join so i can reference the events date correctly

SELECT playerId, SUM(points) top5
FROM
 ( SELECT x.*
        , CASE WHEN @prev = playerId THEN @i:=@i+1 ELSE @i:=1 END i
        , @prev:=playerId
     FROM championshipLeaderboard x
        , (SELECT @prev:=null,@i:=0) vars ORDER BY playerId,points
DESC) a
    WHERE i<=5 AND playerId = 1 AND YEAR(events.date) = YEAR(CURDATE()) 
    GROUP BY playerId
    ORDER BY top5 DESC

See DB fiddle here

Upvotes: 0

Views: 71

Answers (2)

Roger Kreft
Roger Kreft

Reputation: 200

After some discussion (see comments) i came up with this:

SELECT top5.playerId, SUM(top5.points) AS sumOfPoints
FROM (
    SELECT playerId, points
    FROM championshipLeaderboard
    INNER JOIN events ON championshipLeaderboard.eventID = events.eventID
    WHERE playerId = 1
    AND YEAR(events.date) = YEAR(CURDATE()) 
    ORDER BY points DESC
    LIMIT 5
 ) AS top5
 GROUP BY top5.playerId

See also: https://www.db-fiddle.com/f/56ApCUAW6QFJ7vNiyLQqaF/5

Upvotes: 2

Rakib
Rakib

Reputation: 643

You can use inner join with 1=1

    SELECT playerId, SUM(points) top5
FROM
 ( SELECT x.*
        , CASE WHEN @prev = playerId THEN @i:=@i+1 ELSE @i:=1 END i
        , @prev:=playerId
     FROM championshipLeaderboard x
     INNER JOIN events y ON x.event_id = y.event_id
     INNER JOIN (SELECT @prev:=null,@i:=0) vars ON 1=1
     WHERE YEAR(y.date) = YEAR(CURDATE())
     ORDER BY playerId,points DESC
 ) a
    WHERE i<=5 AND playerId = 1  
    GROUP BY playerId
    ORDER BY top5 DESC

Upvotes: 0

Related Questions