MYSQL count a specific entry

Question

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.

@Strawberry I have just editet my query. The new one looks like this: "...order by spielerlevel desc, connectedTime desc limit 20" — mathi1993, Jun 14 '13 at 08:39
Assuming `spielerlevel` and `level` are the same thing, my question still stands. — Strawberry, Jun 14 '13 at 08:41
@Strawberry spielerlevel and connectetTime, not spielerlevel and level. connectedTime = Time in hours how long a player was ingame — mathi1993, Jun 14 '13 at 08:44
@mathi1993 You've lost me, but it seems like others are able to help. — Strawberry, Jun 14 '13 at 08:49

score 4 · Answer 1 · answered Jun 14 '13 at 07:55

4

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.

answered Jun 14 '13 at 07:55

Sirko

72,589
19
149
183

Clever! I was going to suggest using temporary tables. I don't know why you claim it's an approximation, it should return an exact and accurate match. – Dai Jun 14 '13 at 07:56
@Dai The problem exists, when the levels are not distinct. Suppose 5 players all with the same level. According the the given query they might get ranks 11-15. But my query will always return a count of 10 for all of them. That's why I call it an approximation. – Sirko Jun 14 '13 at 08:04
Hi, thanks for your answer. Yeah, you are right. This is a problem, because I have "226,443" accounts in my table. :D Can't I do it with "select username,spielerlevel,member,leader from accounts where adminlevel = 0 AND username = 'Username' order by spielerlevel desc limit 20" and give this entry the number somehow? – mathi1993 Jun 14 '13 at 08:06
@user2485108 Don't you have something like a value "experience" or something, which would (most of the time) avoid these ordering problems? – Sirko Jun 14 '13 at 08:07
@user2485108 You could try it with something like this rank shim: http://stackoverflow.com/q/3333665/1169798 – Sirko Jun 14 '13 at 08:08
@Sirko I have a value called "spielstunden", but the problem is, if guys registers, they have 0 ingame hours, so I can't take this too...hmm. – mathi1993 Jun 14 '13 at 08:13

score 0 · Answer 2 · answered Jun 14 '13 at 07:56

0

This can be a solution :

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

answered Jun 14 '13 at 07:56

jbrtrnd

3,815
5
23
41

MYSQL count a specific entry

2 Answers2