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

Question

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

Answer 1

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.

• bb25: Hi, thank you for your example, however it is returning zero rows for me. See here - dbfiddle.uk/…
• J.D.: @bb25 That's because in your example data on that dbFiddle doesn't have a student_no with the id of 5. If you use the same example data as in your question above, it works, or you can test with a different valid student_no in your example dataset on dbFiddle.
• bb25: Oh yeah sorry it works now.. however I'm just wondering is that two different queries? As I have to put this into my PHP so I need it to be one query. Also, I am getting a little error saying Unrecognized statement type. (near WITH). Do you know what this could be?
• J.D.: @bb25 I think you mean 1 batch, in which case yes it is 1 batch (though not sure of your requirement due to PHP, it should be able to handle multiple batches of queries too). To your second question, I'm not sure where you're plugging in this query but it sounds like other SQL code is running before it (other batches of queries) based on that error. You can mitigate that issue by adding a semicolon ; just before the keyword WITH in the query I provided so it looks like ;WITH.
• bb25: Hi, I have tried your suggestion of the semicolon but it doesn't work. Is there any way you could change the query to not use the WITH ?

Answer 2

You can't use sum in that way, because it reduces everything to one row, so you need always a GROUP BY in your queries.

The next thing is the outer SELECT:

SELECT student_no, points

needs two columns with that name, so the first SELECT has to have those names as colum names or aliases

CREATE TABLE `student_points` (
 `student_no` int(9) NOT NULL,
 `level_id` int(11) NOT NULL,
 `points` int(3) NOT NULL,
 `timestamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
);
INSERT INTO `student_points` (`student_no`, `level_id`, `points`, `timestamp`) VALUES
(12345678, 1, 80, '2021-01-15 16:07:43'),
(12345678, 2, 25, '2021-01-13 17:15:10'),
(12345678, 3, 90, '2021-01-17 22:41:55'),
(12345678, 4, 90, '2021-01-17 22:41:55'),
(40145489, 1, 85, '2021-01-14 21:50:58'),
(40204123, 1, 80, '2021-01-12 15:37:11'),
(40204123, 2, 75, '2021-01-12 15:38:06'),
(40204123, 3, 30, '2021-01-13 22:13:13'),
(40213894, 1, 90, '2021-01-14 21:52:00'),
(40213894, 2, 95, '2021-01-17 22:42:50'),
(40213894, 4, 100, '2021-01-17 22:42:50'),
(40283947, 1, 57, '2021-01-14 21:50:14'),
(40334891, 1, 95, '2021-01-14 21:54:25'),
(40829379, 1, 70, '2021-01-14 21:50:38'),
(45216227, 1, 97, '2021-01-16 19:05:16');

SELECT 
 student_no, points
FROM
 (
 (SELECT 
 student_no, SUM(points) points, 1 orderby
 FROM student_points a
 GROUP BY student_no
 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
 GROUP BY student_no
 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 , points DESC

student_no | points
---------: | -----:
 40213894 | 285
 12345678 | 285
 40204123 | 185
 45216227 | 97
 40334891 | 95
 40145489 | 85

db<>fiddle here

• bb25: Thank you :) Can I ask, this query doesn't take the timestamp column into consideration does it? Also if you don't mind could you maybe explain the 'orderby' in your query? I can't understand what that part is doing
• nbk: no, the time isn't inclued, you must show me the end result you want. For the orderby, you wanted the 40204123 before a student number that have the same points, that is why the first select chooses 2 or 3 with are directly above the wanted student then comes the selected one which is the sencond block and then comes the 2 or 3 that have the same or less points.
• bb25: Sorry but I still don't understand what the 1 orderby and 2 orderby etc means? It's as if they're column names but I dont understand what they do
• nbk: orderby i is needed because when you have more than one row that has the same SUM(POINTS) so that your wanted result has the sum points of 40204123 vefore the student_no with the same point, a result set is unordered that is by definition so the order of student_no can not be guaranteed as long you don't have a column that has the correct sorting and that is otderby