Position of entry in sqlite ordered table

All we need is an easy explanation of the problem, so here it is.

I’m attempting to order a table and return the position of a row in that ordered table. Just for context, I’m developing a leveling system for a discord bot and trying to implement a rank command. The command will show the user’s xp in relation to the rest of the server. eg. "You have 25th most xp in this server". I’m able to order the table with SELECT * FROM users ORDER by xp DESC but struggling to get the entry’s position. I’d appreciate it if you could give any advice

My table structure is as follows:

CREATE TABLE "users" (
"guild_id"  INTEGER NOT NULL,
"user_id"   INTEGER NOT NULL,
"xp"    INTEGER DEFAULT 0,
PRIMARY KEY("guild_id","user_id")

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can use ROW_NUMBER for this.

SELECT YourRank
FROM (
    SELECT *,
      ROW_NUMBER() OVER (ORDER BY xp DESC) AS YourRank
    FROM users
) u
WHERE u.user_id = @someId;

If you want tied rankings, you can also use RANK or DENSE_RANK

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply