How to split sql query results into new incremental tables

I am using the following code to create a HTML table which works great to produce a table ordered by position based on points. However, I would like to sub divide the results into different tables with incremental names. For example, the first 20 results will be Table 1, the next 20 records will be table 2 and so on. I need it to be dynamic because the total number of records will be different each time the query runs.

SELECT name,team, points,
         (@row_number:=@row_number + 1) AS position
    
   FROM results AS t, (SELECT @row_number := 0) AS r
   ORDER BY points DESC 

I would appreciate advice on the best way to achieve this. Thanks

Answer

Test this (for 20 rows per page/table):

SELECT name,team, points,
       (@row_number := (@row_number MOD 20) + 1) AS position,
       (@page_number := @page_number + (@row_number = 1)) AS page
FROM results AS t, (SELECT @row_number := 0б @page_number := 0) AS r
ORDER BY points DESC 

PS. The points column values must be unique, if not then the order of the rows with the same value is unpredictable (in such case you may expand sorting expression for to provide uniqueness, for example, use ORDER BY points, id).

---

10.4.14-MariaDB – f1inshot

Your query should be

SELECT name,team, points,
       ROW_NUMBER() OVER (ORDER BY points) AS position
FROM results AS t
ORDER BY position;

and respectively

SELECT name,team, points,
       (ROW_NUMBER() OVER (ORDER BY points) MOD 20) AS position,
       ((ROW_NUMBER() OVER (ORDER BY points) + 19) DIV 20) AS page
FROM results AS t
ORDER BY page, position;

And the same – if ponts is not unique use expanded ORDER BY in window definitions.