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)
Code:
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
Code:
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
Code:
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 -------------------------- Edit: no worries i figured it out
Code:
select count(*)+1 from (select count(*) from table group by fieldB having sum(fieldA) > (select sum(fieldA) from table where fieldB=24)) rank;
Last edited by djlee; 03-13-2009 at 07:34 AM..
|
