0

My table looks like that:

Field Type
id     int            
user_id   int            
date   datetime            
points   int            

With this query SELECT SUM(points) AS total, DATE_FORMAT(date,'%Y') AS year FROM table WHERE user_id = 2 GROUP BY yearmonth ORDER BY year ASC I got sum of all points grouped by year.

I would like to take only five highest orders per group (instead of all from this query).

I was trying to find the answer via search, but I can't find the way to get things to work. I know it must be something with row_number, but I can't get it to work. Mysql is 5.7.

Can somebody point me in the right direction?

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Dave
  • 1

1 Answers1

1

Because you use MySQL 5.7 (and not 8.0 or later to use partitions), a join on the same table could be a solution:

SELECT
    SUM(a.points) AS total,
    DATE_FORMAT(a.date, '%Y') AS YEAR,
    COUNT(b.id) + 1 AS row_number 
FROM table AS a 
    LEFT JOIN a AS b ON a.id = b.id
WHERE
    a.user_id = 2 
GROUP BY
    a.YEAR 
    HAVING COUNT(b.id) + 1 <= 5
ORDER BY
    a.YEAR ASC, a.points DESC
Valeriu Ciuca
  • 2,084
  • 11
  • 14