Get record position in SQL

Question

I have a rank system on my Discord bot, and I am trying to display a message like ('You are rank #5') So I need to query my database, but I am not that great with SQL (I use better-sqlite3) What I have tried is evidently wrong. Can someone help me, please? My attempt:

    const userRank = db.prepare('SELECT count(*) FROM scores WHERE points <= 113 AND guild = ? ORDER BY points DESC').all(message.guild.id);
    console.log(userRank);

I would like the console.log to output '5' in this case but the current output says '1' (check photo for database records)

Would it make more sense to use a `>=` to see how many more records have more points or matching points? If you dont care about ties, then `>` should work. You are on the right track already it seems. — Andrew Nolan, Mar 11 '20 at 19:17
Yes, I made that mistake, see my answer to see how stupid I was. — Ragnar Lothbrok, Mar 11 '20 at 19:46

score 1 · Answer 1 · answered Mar 11 '20 at 19:22

1

If you want it to say 5, you should remove guild from WHERE clause and flip comparison operator.

SELECT count(*) FROM scores WHERE points >= 113 ORDER BY points DESC

answered Mar 11 '20 at 19:22

Nate H

76
6

score 0 · Answer 2 · answered Mar 11 '20 at 19:45

I am just an idiot, my code was almost right, change <= to >= and it was working fine, however, when looking at my database, I had not filtered it by guild. So it was giving the correct answer, I was just looking at the wrong table. answer:

    const userRank = db.prepare('SELECT count(*) FROM scores WHERE points >= 64 AND guild = ? ORDER BY points DESC').all(message.guild.id);

Get record position in SQL

2 Answers2