How to paginate a sql table without cutting on a certain column

user_3960404 0
Viewed 71

Imagine this sample table data ordered by country name:

CustomerID  Country
12          Argentina
54          Argentina
20          Austria
59          Austria
50          Belgium
76          Belgium
77          Belgium
15          Brazil
21          Brazil
31          Brazil
34          Brazil
88          Brazil
10          Canada
42          Canada
51          Canada
73          Denmark
74          France
84          France
85          France
1           Germany
6           Germany
17          Germany
37          Ireland
27          Italy
49          Italy
66          Italy
2           Mexico
3           Mexico

How could I paginate it by a limit of no more than 10(has exceptions) with out it returning pages that cut in the middle of country groups. Here is the expected result

variable with page = 1 returns

12          Argentina
54          Argentina
20          Austria
59          Austria
50          Belgium
76          Belgium
77          Belgium

variable with page = 2 returns

15          Brazil
21          Brazil
31          Brazil
34          Brazil
88          Brazil
10          Canada
42          Canada
51          Canada
73          Denmark

variable with page = 3 returns

74          France
84          France
85          France
1           Germany
6           Germany
17          Germany
37          Ireland

variable with page = 4 returns

27          Italy
49          Italy
66          Italy
2           Mexico
3           Mexico

An exception to the limit of 10 is if there are more than 10 rows with the same country.

I tried a couple things with limit and offset but still haven't found any clean/simple query. I am doing this for chunking purposes. Any help is much appreciated. You can play around with the data HERE!

mysql sql
1 Answers

You can do this in MySQL using variables. This is quite challenging because you need to keep track of the page and the row number within the page. But, MySQL only really supports one expression per variable. More specifically, it doesn't guarantee the order of evaluation of expressions in a SELECT.

The logic is then to add a new column for the page, which you can use for pagination. The logic is something like this:

select t.*,
       @p := (case when @c = country
                   then (case when @rn = 10  -- a county has more than 10 rows
                              then (case when (@rn := 1) = null then null -- never happens
                                         else (@p := @p + 1) 
                                    end)
                              else (case when (@rn := @rn + 1) = null then null -- never happens
                                         else @p
                                    end)
                         end)
                    when (@rn + cnt > 10)  -- start a new page
                    then (case when (@rn := 1) = null then null -- never happens
                               else (@p := @p + 1)
                          end)
                    else (case when (@rn := @rn + 1) = null then null -- never happens
                               else @p
                          end)
             end) as page
from (select t.*, c.cnt
      from t join
           (select country, count(*) as cnt
            from t
            group by country
           ) c
           on t.country = c.country
      order by t.country, t.CustomerID
    ) t cross join
    (select @rn := 0, @p := 0, @c := '') params
order by page, t.country, t.CustomerId;

Here is a db<>fiddle that shows the code working (in at least some cases).

user_11440350
Related