[Yoav]

Hello, i'm trying to create an ingame ranking for my server in TF2.
My DB looks like this:

(table: player_kills)
auth ------------- value
STEAM:2313213 -- 35

now i'm on OnClientAuthorized and im using this query:

SELECT value FROM player_kills WHERE auth="STEAM:2313213"

how can i get the ranking of the player by its kills?

[Monkeys]

You could read all the values of all players, sort them by value, get the top # and list them.

[Yoav]

Would you show me an example please?

[Scone]

To find the rank of a player:

SET @rownum := 0;
SELECT rank FROM (SELECT @rownum := @rownum+1 AS rank, auth FROM player_kills ORDER BY value DESC) AS ranks WHERE auth = "STEAM_0:0:XXX";

Although this is slow and messy. It may be better to periodically calculate all players' ranks and store them in a separate table, so they can be looked up quickly.

[Monkeys]

Wouldn't it be easier to just

SELECT TOP 10 value, auth
FROM player_kills
ORDER BY value ASC

Or is my SQL that rusty?

This returns the top 10 values and authentications to match.
(Probably need to add the ' wherever it's needed, though. They seem to fuck up wherever I put them)

[sfPlayer]

Something like

select count(*) as rank
from player_kills
where value > (select value from player_kills where auth = '..steamid..')

rank +1 is the actual rank you are looking for. It's just counting how many players have more kills.

Make sure value is an index.

[Scone]

Originally Posted by sfPlayer select count(*) as rank from player_kills where value > (select value from player_kills where auth = '..steamid..')

Can't believe I didn't think of that! Much nicer than my solution

[NUdH3]

I use the same query.

With phpmyadmin it works, but in SourceMod i get a Syntax Error.

Format(query, sizeof(query), "SET @rownum := 0; SELECT rank FROM (SELECT @rownum := @rownum+1 AS rank, auth FROM player_kills ORDER BY value DESC) AS ranks WHERE auth = '%s'", pauth[8]);

I think thats not possible because it is a SQL_TQuery. I tested:

SQL_FastQuery(hDatabase, "SET @rownum := 0");
Format(query, sizeof(query), "SELECT rank FROM (SELECT @rownum := @rownum+1 AS rank, auth FROM player_kills ORDER BY value DESC) AS ranks WHERE auth = '%s'", pauth[8]);
SQL_TQuery...

that works, better solutions?