Mysql – Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL

join;mariadbMySQLunion

Say I have a table like so:

+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 3 |   1   |  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 show 5 of these rows in a table (in html), 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). Also where level=1. This will be like a score board but only showing the user's score with the two above and two below.
So because scores can be the same, 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.

E.g. say the user is id=5, I want to show

+---+-------+------+---------------------+
|id | level |score |      timestamp      |
+---+-------+------+---------------------+
| 4 |   1   |  70  | 2021-01-14 21:50:38 |
| 8 |   1   |  55  | 2021-01-14 09:20:00 |
| 5 |   1   |  50  | 2021-01-13 12:32:11 |
| 7 |   1   |  50  | 2021-01-14 17:15:20 |
| 2 |   1   |  45  | 2021-01-15 10:50:38 |
| 1 |   1   |  20  | 2021-01-14 13:10:12 |
+---+-------+------+---------------------+

Note that id=7 is below id=5

I am wondering does anyone know a way of doing this?

I have tried this below but it is not outputting what I need (it is outputting where level_id=2 and id=5, and the other rows are not in order)

((SELECT b.* FROM table a JOIN table b ON b.score > a.score OR (b.score = a.score AND b.timestamp < a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score ASC, timestamp DESC LIMIT 3)
 UNION ALL 
 (SELECT b.* FROM table a JOIN table b ON b.score < a.score OR (b.score = a.score AND b.timestamp > a.timestamp)
  WHERE a.level_id = 1 AND a.id = 5 ORDER BY score DESC, timestamp ASC LIMIT 2)) 
order by score

If it is easier to output all rows in the table, say where level = 1, so it is a full score board.. and then do the getting a certain row and two above and below it using PHP I'd also like to know please 🙂 ! (possibly thinking this may keep the SQL simpler)?

Best Answer

The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement

CREATE TABLE table1
    (`id` int, `level` int, `score` int, `timestamp` varchar(19))
;
    
INSERT INTO table1
    (`id`, `level`, `score`, `timestamp`)
VALUES
    (4, 1, 70, '2021-01-14 21:50:38'),
    (3, 1, 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')
;

SELECT 
    `id`, `level`, `score`
FROM
    ((SELECT 
        `id`, `level`, `score`, 1 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` > (SELECT 
                `score`
            FROM
                table1
            WHERE
                id = 5)
    ORDER BY `score` ASC
    LIMIT 2) UNION ALL (SELECT 
        `id`, `level`, `score`, 2 orderby
    FROM
        table1 a
    WHERE
        id = 5) UNION ALL (SELECT 
        `id`, `level`, `score`, 3 orderby
    FROM
        table1 a
    WHERE
        level = 1
            AND `score` <= (SELECT 
                `score`
            FROM
                table1
            WHERE
                id = 5)
            AND id <> 5
    ORDER BY `score` DESC
    LIMIT 3)) t1
ORDER BY orderby ASC , score DESC

id | level | score
-: | ----: | ----:
 4 |     1 |    70
 8 |     1 |    55
 5 |     1 |    50
 7 |     1 |    50
 2 |     1 |    45
 1 |     1 |    20

db<>fiddle here

Related Question