0

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")
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.