Search code examples
mysqlarrayslistleaderboard

How to build FourSquare like leaderboard (users above and below you)

I'm looking for a way to implement Foursquare's style of leaderboard where instead of showing you the top 10 for example it shows the you 2 people above you and 2 people below you between your friends.

I'm hoping to avoid having to pull down everyones scores and do some array manipulation. Is there anyway I can do this with a smart SQL statement?

Solution

  • You could (assuming table USER has an integer column SCORE):

    select * from USER where SCORE < myscore order by SCORE DESC limit 2;

select * from USER where SCORE >= myscore order by SCORE ASC limit 2;

    Is this what you mean?