CODEWITHSUNDEEP
pythonsqlite
Directory
Directory

Reputation: 143

Anyway to get the position of a specific row in an ordered query in Sqlite3?

Let's say for my sqlite3 based application, there is a leaderboard. All users have a level and there are thousands of users. If i wanted to get the leaderboard, I would query for users with a limit of 10, and order by descending level. Now that works. But what if I wanted there to be an 11th user in the leaderboard, but its not the person with the 11th biggest level. It's the user who executed to check the leaderboard. I want their to be a way for them to pull a leaderboard and see the top 10 level users, but see themselves in the 11th place, with what place they are. That's what i do not know how to do. How do i get the place, the position of that user compared to everyone else, without querying the entire database. My application has nearly 100k rows of users if it's too inefficient to select the entire database.

Here's an example of what i want it to look like (made the leaderboard 5 long instead of 10 for simplicity)

1. BobBobman123 (lvl 104)
2. Jin73 (lvl 99)
3. iefa44 (lvl 78)
4. jobSteve_8 (lvl 68)
5. david4 (lvl 61)
143. harrypotter64 (lvl 7)

The thing i want to accomplish would be for example, to be able to get the place of harrypotter64 (the person who checked the leaderboard). The place in this example would be 143.

Upvotes: 1

Views: 271

Answers (1)

Shawn
Shawn

Reputation: 52549

Window functions to the rescue! (Requires sqlite 3.25 or newer). Something like

WITH ranked AS
 (SELECT dense_rank() OVER (ORDER BY level DESC) AS position
       , username, level
  FROM leaderboard)
SELECT *
FROM ranked
WHERE position <= 10 OR username = 'harrypotter64'
ORDER BY position

With an index on leaderboard(level DESC) for efficiency.

Upvotes: 1

Related Questions