theunreal — 2014-05-17T04:38:50-04:00 — #1
I have a table with the following rows for example:
and I want to show the user what hes rank (in numbers).
The total power is the Strength+Speed+Power.
If there are 2 users:
user1: 20 strength, 10 speed , 10 power (sum: 40)
user2: 30 strength,15 speed, 15 power (sum:60)
it will show to user1: you rank is #2.
How I can build such a query that does that and give me the count or orders by the sum so I can run a loop until it find the right user?
Thanks in advance!
r937 — 2014-05-17T07:30:04-04:00 — #2
which database? because if it's mysql you won't be able to use window functions
theunreal — 2014-05-17T13:17:07-04:00 — #3
PHP & MYSQL
mittineague — 2014-05-17T14:08:22-04:00 — #4
Maybe it would be better to query the
@theunreal; Are you looking for a database or a PHP solution?
i.e. should this thread be moved to the PHP forum?
theunreal — 2014-05-18T12:45:02-04:00 — #5
I run it with PHP but I need to know which query I should run, sothe solution releated to both of the forums :S
r937 — 2014-05-18T13:31:38-04:00 — #6
, t.strength +
t.power AS superpower
, ( SELECT COUNT(*) + 1
FROM test_ranks AS z
WHERE z.strength + z.speed + z.power
> t.strength + t.speed + t.power ) AS rank
FROM test_ranks AS t
WHERE t.userid = 937
no loop required!!