[SOLVED] MySQL SUM Ranking

[djlee]

Ok this is the code ive been working from. This is what i created to get the "rank" of a user depending upon a select field (uid example used, i obviously have a variable there)

 

SELECT count(*) FROM players WHERE score >= (SELECT score FROM players WHERE uid=44 LIMIT 1)

 

problem is i am now working on a different sub-system where there are multiple records for each UID, i need to get the rank depending upon the sum of a field. Now im no MySQL expert, i only learned what ive needed in order to do what i do up to this point where im stumped. So i first tried

 

SELECT count(*) FROM players WHERE sum(fieldA) >= (SELECT sum(fieldA) FROM players WHERE uid=44 LIMIT 1) GROUP BY uid

 

I obviously got a error about invalid use of grouping. but i posted that there as i think it gives the best explaination of what im trying to do, after this i tried

 

SELECT count(*) FROM players GROUP BY uid HAVING sum(fieldA) >= (SELECT sum(fieldA) FROM players WHERE uid=44 LIMIT 1)

 

While i got no errors, that returned a resultset thats incorrect, on one test uid it returned a single count(*) which at that point i was overjoyed of course, but then with another uid it returned a resultset of 3, which obviously isnt correct as i only shud have one in the resultset (the rank number)

 

Does anyone know where im going wrong, can someone help me out ??

 

Any help would be greately appreciated

[kickstart]

Hi

 

Think you middle solution should have worked, but can't see why you would need to use a "group by".

 

There was a similar ranking thread a couple of days ago which might help:-

 

http://www.phpfreaks.com/forums/index.php/topic,241174.msg1129114.html#msg1129114

 

All the best

 

Keith

[djlee]

Hi, thanks for the reply but that thread is basically aiming towards my first sql statement i posted, where it selects a rank  (that thread also gets other fields but i dont need that bit) depending upon the value of a single field. What i need to do is select the rank depending upon the sum of a field as there will be multiple entries for each user.

 

I tried that middle query without the group by and i get the "invalid use of group function" error.

 

Also before anyone tells me to do so, i cannot just keep updating fieldA with new data in order to use the first SQL i posted as each record also contains unique data in other fields used by other systems.

 

I may be able to get around it with a loop but with the almost definate chance of this table growing into thousands of records i was looking for a single SQL statement to be much more efficient than a loop which would use at least one extra query per uid.

 

Thanks for the reply though, every reply hopefully gets me closer to an answer

[djlee]

cant find an edit button so just to let u kno i sorted it

 

select count(*)+1 from (select count(*) from table group by fieldB having sum(fieldA) > (select sum(fieldA) from table where fieldB=24)) rank;