I have a MySQL problem that I can't figure out.

I run a query:

SELECT id, totalsum FROM table ORDER BY totalsum DESC

This could give me the following result:

1, 10000
4, 90000
8, 80000
3, 50000
5, 40000
    

What is need is a code that should work something like this:

SELECT id, totalsum 
FROM table ORDER BY totalsum DESC
START LISTING FROM id=8 AND CONTINUE TO THE END OF RESULT / LIMIT

Resulting in someting like this

8, 80000
3, 50000
5, 40000
    

I can not use this query:

SELECT id, totalsum 
FROM table 
WHERE id>=8
ORDER BY totalsum DESC

Because the id could be both < and >.

Have tried using LIMIT AND OFFSET but that resulting in very slow speed.

Any advice pointing me in the right direction will be appreciated!

CodePudding user response：

Here's a way to do it:

1. Assign each row a row_num based on totalsum in descending order (CTE)
2. Select from the above where row_num >= the row_num of id=8

create table a_table (
    id      int,
    total   int);
    
insert into a_table values
(1, 100000),
(4, 90000),
(8, 80000),
(3, 50000),
(5, 40000);


with cte as (
select id,
       total,
       row_number() over (order by total desc) as row_num
  from a_table)
select *
  from cte
 where row_num >= (select row_num from cte where id=8);

Result:

id|total|row_num|
-- ----- ------- 
 8|80000|      3|
 3|50000|      4|
 5|40000|      5|