-2

I'm trying to figure out how to Select a specific number of rows from a MySQL table based on WHERE clause. I have a table with 10 dummy users, I want to get 2 previous and 2 next users of specific user with their ranks.

user_id  |  points
==================
     10        200
      4        130
      2        540
     13        230
     15        900
     11        300
      3        600
     17        110
     20        140
      1        430
      5        800

I achieved adding a column for ranking like:

user_id  |  points  |  rank
===========================
     15        900        1
      5        800        2
      3        600        3
      2        540        4
      1        430        5
     11        300        6
     13        230        7
     10        200        8
     20        140        9
      4        130       10
     17        110       11

But the problem is that I want only 5 rows. Suppose I'm retrieving data for user with user_id = 11. The output should look like this:

user_id  |  points  |  rank
===========================
      2        540        4
      1        430        5
     11        300        6
     13        230        7
     10        200        8

where user_id = 11 is in the centre with 2 rows above and 2 below. I have tried nesting UNIONS and SELECT statements but nothing seems to work properly.

Muhammad Talha
  • 165
  • 1
  • 8
  • 3
    Please don't spam tag RDBMS - tag the single RDBMS you are using and require a solution in. – Dale K Jul 26 '22 at 22:04
  • What is MySQL version? Please run `SELECT version();` to check. And could you post at least 1 of your attempted query? – FanoFN Jul 26 '22 at 23:45

1 Answers1

1

Here's a suggestion if you're on MySQL 8+:

WITH cte AS (
SELECT user_id, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS Rnk
    FROM mytable)
SELECT cte2.user_id,
       cte2.points,
       cte2.Rnk
FROM cte cte1 
 JOIN cte cte2
 ON cte1.user_id=11
 AND cte2.Rnk >= cte1.Rnk-2
  AND cte2.Rnk <= cte1.Rnk+2

Using common table expression (cte) then do a self join with condition of user_id=11 as base to get the Rnk value of -2 and +2.

Demo fiddle

Since you're on older MySQL version, here's what I can suggest:

SET @uid := 11;
SET @Rnk := (SELECT Rnk 
              FROM
                (SELECT user_id, points,
                        @r := @r+1 AS Rnk
                  FROM mytable 
                   CROSS JOIN (SELECT @r := 0) r
                   ORDER BY points DESC) v
                  WHERE user_id = @uid);
              
SELECT user_id, points, Rnk
  FROM
(SELECT user_id, points,
        @r := @r+1 AS Rnk
                  FROM mytable 
                   CROSS JOIN (SELECT @r := 0) r
                   ORDER BY points DESC) v
 WHERE Rnk >= @Rnk-2
   AND Rnk <= @Rnk+2;

If you will only use user_id as base, then the only part here you need to change is the SET @uid. The remaining queries are just fulfilling your condition of getting two positions above and below the rank retrieved according to the user_id. The base query in SET @Rnk is the same as the base query for the last one. The idea is to assign @Rnk variable with Rnk position of user_id=11 then use it in WHERE condition for the last query.

I'm not aware if there's any online fiddle still using MySQL 5.1 but here's probably the closest version to it, MySQL 5.5 demo fiddle.

FanoFN
  • 6,815
  • 2
  • 13
  • 33