MySQL: Select column and average with offset

Question

I have a table feedback with the following columns:

`id` bigint NOT NULL AUTO_INCREMENT
`uri` varchar(191) NOT NULL
`score` tinyint NOT NULL
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,

What I'm trying to get is the paginated set of records with average score for a unique uri.

My current statement is:

SELECT `uri`, avg(score) as avg_score 
FROM `feedback` 
WHERE `created_at` BETWEEN '2023-02-06 00:00:00' AND '2023-03-06 23:59:59' 
GROUP BY `uri` 
ORDER BY `avg_score` desc 
LIMIT 8 OFFSET 0

which gives me the result of:

| uri                                       | avg_score |
|-------------------------------------------|-----------|
| /conta                                    |    5.0000 |
| /calendar                                 |    5.0000 |
| /panels                                   |    5.0000 |
| /about                                    |    5.0000 |
| /calendar?query=for%20crying%20out%20loud |    5.0000 |
| /contacts                                 |    5.0000 |
| /contactss                                |    5.0000 |
| /contactsd                                |    5.0000 |

And in that format it works just fine, however the problem occurs when I increment the offset:

SELECT `uri`, avg(score) as avg_score 
FROM `feedback` 
WHERE `created_at` BETWEEN '2023-02-06 00:00:00' AND '2023-03-06 23:59:59' 
GROUP BY `uri` 
ORDER BY `avg_score` desc 
LIMIT 8 OFFSET 8

Now I'm getting duplicates of the records from the previous page (notice /about)

| uri      | avg_score |
|----------|-----------|
| /contad  |    5.0000 |
| /cob     |    5.0000 |
| /cobs    |    5.0000 |
| /core    |    5.0000 |
| /about   |    5.0000 |
| /contact |    4.9545 |
| /        |    3.4000 |

Any way to make it work?

Answer 1

you might try sub-query way and distinct the duplicated one in the sub-query.

sample code likes below:

select uri, avg_score from (
   SELECT distinct `uri`, avg(score) as avg_score 
   FROM `feedback` 
   WHERE `created_at` BETWEEN '2023-02-06 00:00:00' AND '2023-03-06 23:59:59' 
   GROUP BY `uri` 
   ORDER BY `avg_score` desc 
 )
 LIMIT 8 OFFSET 8