I have been given a leaderboard, I need to return the row correspond to 4th to 8th rank in a leaderboard?

id  name    score
1   gongy   3001
2   urandom 2401
3   eduardische 2477
4   Gassa   2999
5   bcc32   2658
6   Alex_2oo8   6000
7   mirosuaf    2479
8   Sparik  2399
9   thomas_holmes   2478

The query I have is not being accepted

select b.name from
(
    select a.name as name ,a.score as score from 
    (
    select name,score from leaderboard order by score desc limit 8
    )a
    order by a.score 
    limit 5 
) b
order by b.score desc;

CodePudding user response：

You can use LIMIT OFFSET

select id,
       name,
       score
from leaderboard
order by score desc 
limit 4 offset 3  ; --- fetch 4 records, begin with record 4 (OFFSET 3)

https://dbfiddle.uk/Ys-3jC4L

Above query skips the first 4 rows and limit the result to 4 make it from 4th to 8th

CodePudding user response：

select x.id,x.name,x.score
from
(
  select id,name,score,
   row_number()over(order by score desc)xcol
   from your_table
)x where x.xcol >=3 and x.xcol<=8

May be something like this ?