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.
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
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂