Mariadb – Get rows above and below a certain row, based on two criteria SQL (with SUM)

mariadbsumunion

Say I have a table like so:

+----------+---------+------+---------------------+
|student_no|level_id |points|      timestamp      |
+----------+---------+------+---------------------+
|     4    |    1    |  70  | 2021-01-14 21:50:38 |
|     3    |    2    |  90  | 2021-01-12 15:38:0  |
|     1    |    1    |  20  | 2021-01-14 13:10:12 |
|     5    |    1    |  50  | 2021-01-13 12:32:11 |
|     7    |    1    |  50  | 2021-01-14 17:15:20 |
|     8    |    1    |  55  | 2021-01-14 09:20:00 |
|    10    |    2    |  99  | 2021-01-15 10:50:38 |
|     2    |    1    |  45  | 2021-01-15 10:50:38 |
+----------+---------+------+---------------------+

What I want to do is find the total points for each person (student_no), and show 5 of these rows in a table, with a certain row (e.g. where id=5) in the middle and have the two rows above and below it (in the correct order – with highest at the top). This will be like a score board but only showing the user's total points (over all levels) with the two above and two below. So because points could be equal, the timestamp column will also need to be used – so if two scores are equal, then the first person to get the score is shown above the other person.

I have tried this below but it is not outputting what I need.

SELECT 
    student_no, SUM(points)
FROM
    (
    (SELECT 
        student_no, SUM(points), 1 orderby
    FROM student_points a
    HAVING
        SUM(points) > (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
    ORDER BY SUM(points) ASC LIMIT 3) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 2 orderby
    FROM student_points a
    WHERE student_no = 40204123) 
     
     UNION ALL 
     
     (SELECT student_no, SUM(points), 3 orderby
    FROM student_points a
    HAVING
        SUM(points) <= (SELECT SUM(points) FROM student_points WHERE student_no = 40204123)
            AND student_no <> 40204123
    ORDER BY SUM(points) DESC LIMIT 3)
    ) t1
ORDER BY orderby ASC , SUM(points) DESC

This is a dbfiddle of what I am trying:
https://dbfiddle.uk/?rdbms=mariadb_10.4&fiddle=5ada81241513c9a0be0b6c95ad0f2947

Best Answer

You should use the ROW_NUMBER() or LAG() and LEAD() window functions to get the previous and next N number of rows relative to the current row.

For example:

WITH leaderboard AS
(
    SELECT student_no, SUM(points) AS points_total, ROW_NUMBER() OVER (ORDER BY SUM(points) DESC, timestamp ASC) AS leaderboard_rank
    FROM student_points
    GROUP BY student_no
),
middle_score AS 
(
    SELECT leaderboard_rank
    FROM leaderboard
    WHERE student_no = 5
)

SELECT L.points_total
FROM leaderboard L
INNER JOIN middle_score M
    ON L.leaderboard_rank >= M.leaderboard_rank - 2
    AND L.leaderboard_rank <= M.leaderboard_rank + 2
ORDER BY L.leaderboard_rank

Note I assume when you say "e.g. where id=5" in your example, you're referring to studsnt_no, otherwise you can replace student_no with whichever field you were referring to in my example. You can find additional examples of how to use window functions here.