CODEWITHSUNDEEP
sqlnode.jsdiscord.jsbetter-sqlite3
Ragnar Lothbrok
Ragnar Lothbrok

Reputation: 306

Get record position in SQL

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)

databse

Upvotes: 0

Views: 115

Answers (2)

Ragnar Lothbrok
Ragnar Lothbrok

Reputation: 306

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);

Upvotes: 0

Nate H
Nate H

Reputation: 76

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

Upvotes: 1

Related Questions