CODEWITHSUNDEEP
phpmysqldatabasecodeigniter
Janessa Bautista
Janessa Bautista

Reputation: 294

Ranking Leaderboard using MYSQL Query

i am having a problem, i am not that good in logic, so i am trying to do this by just using queries only,

i have a list of data, where i need to get their ranking, but the problem is, i need to get the first two upper to me and the last two who is lower to me.

for example

id| name |   score
1   bob       20
2   anna      10
3   jose      30
4   boni      30
5   lea       100
6   leo       10
7   qwertina  90
8   josh      50
9   king      40
10  queen     10

imagine that my id value as a user is 7

so if i log in and my id is 7

i need to get the output of

id| name |   score
5   lea       100
6   leo       10
7   qwertina  90
8   josh      50
9   king      40

is this possible in mysql query? any help would be really appreciated, i am really stuck with this problem.

Upvotes: 0

Views: 169

Answers (2)

delboy1978uk
delboy1978uk

Reputation: 12365

Not sure of a MySQL only solution, but in pseudo php code

Grab records ordered by score
set a counter at 0
for loop starting 0, <= count of results
  if the id your id? 
    if the loop index < 3 ? // there aren't two people ahead of you
      return rows 0 to 4
    elseif the loop index > count -3 ? // you are one of the last
      return rows count -4 to count
    else
      return rows index -2 to index + 2  // you have 2 above and below you

Try that and if you get stuck post your PHP

Upvotes: 0

Gosfly
Gosfly

Reputation: 1300

You seem to base your logic on id's which is quite strange but here we are :

SELECT id, name, score
FROM yourTable
WHERE id BETWEEN yourId-2 AND yourId+2

Upvotes: 1

Related Questions