CODEWITHSUNDEEP
phpmysql
mathi1993
mathi1993

Reputation: 1

MYSQL count a specific entry

I have a "Top-20" System in my Webinterface where 20 players got listed and sorted by level. Now i want to show the guy, who is logged in, at the bottom of the table and display which entry he is. I have made it with photoshop to show how it should be:

enter image description here

My question is, how can i get the number of this specific entry? For the top 20 i have following query: 

select username,spielerlevel,member,leader 
from accounts 
where adminlevel = 0 
order by spielerlevel desc 
limit 20

Thanks.

Upvotes: 0

Views: 80

Answers (2)

jbrtrnd
jbrtrnd

Reputation: 3833

This can be a solution :

SELECT COUNT(*)+1
FROM accounts
WHERE adminlevel = 0
AND spierlerlevel < (
    SELECT spierlerlevel 
    FROM accounts 
    WHERE username = "CURRENT USER NAME"
);

Upvotes: 0

Sirko
Sirko

Reputation: 74036

An approximation of his place in the list is given by this:

SELECT COUNT(1)
FROM accounts
WHERE adminlevel = 0 
  AND spielerlevel<[level]

(Replace [level] with the respective user's level.)

The problem is just with many user having the same level. But this might be a problem in your example query as well.

Upvotes: 4

Related Questions