By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();
create table userData (recordDate date ,userName varchar(10), score FLOAT);
insert into userData (recordDate, userName, score)
values
('2020/8/1','Chris', 78),
('2021/8/2','Aamir', 77),
('2021/8/3','Alex', 76),
('2021/8/1','Ali', 78);
-- more users
insert into userData (recordDate, userName, score)
values
('2019/8/1','user1', 68),
('2019/8/2','user2', 67),
('2019/8/3','user3', 66),
('2019/8/1','user4', 58),
('2019/8/1','user5', 64),
('2019/8/1','user6', 43),
('2019/8/1','user7', 55);
| version() |
|---|
| 8.0.27 |
set @page := 1;
set @limit := 2;
WITH ranks AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS score_rank
FROM userData
)
SELECT *
FROM ranks
WHERE score_rank BETWEEN (@page - 1) * @limit + 1 AND @page * @limit
OR userName = 'Alex'
ORDER BY score_rank
| recordDate | userName | score | score_rank |
|---|---|---|---|
| 2020-08-01 | Chris | 78 | 1 |
| 2021-08-01 | Ali | 78 | 2 |
| 2021-08-03 | Alex | 76 | 4 |