[SOLVED] My Rank

[jaymc]

I have a table which contains 2 fields, username & score

 

I want to find my score and the amount of people that have a better score than me, which will essentually tell me where I am ranked

 

Can this be done in one query?

[Maq]

SELECT username FROM table WHERE score > (SELECT score FROM table WHERE username = '$username');

 

[jaymc]

SELECT username FROM table WHERE score > (SELECT score FROM table WHERE username = '$username');

 

That will pull out all records with a better score than me, but it does not return my own score which I need it to

 

Can you expand?

 

 

 

I tried this

SELECT count(*), (SELECT minutes_online_month FROM cache WHERE username = 'jaymc') as score
FROM cache
WHERE minutes_online_month > score

 

But it doesnt like the > score part and I dont want to have 3 queries in one..

[fenway]

Switch to HAVING, not WHERE.

[jaymc]

Hmm, not to sure how to use that. I tried

 

SELECT count(*), (SELECT minutes_online_month FROM cache WHERE username = 'jaymc') as score
FROM cache
HAVING score < minutes_online_month

[fenway]

Wait a sec... won't score always be equal to minutes_online_month in your example above?

[jaymc]

score will = my own personal minutes_online_month in the table cache

WHERE minutes_online_month > score will be everyone elses minutes_online_month in the table

[fenway]

Hmm.

 

How about:

 

SELECT count(*), yourScore
INNER JOIN 
(SELECT minutes_online_month FROM cache WHERE username = 'jaymc') as yourScore
FROM cache
WHERE minutes_online_month > yourScore

[jaymc]

syntax error near as yourScore

[fenway]

Sorry, my bad, multi-tasking:

 

SELECT count(*), c2.yourScore
FROM cache AS c1
CROSS JOIN
(SELECT minutes_online_month AS yourScore FROM cache WHERE username = 'jaymc') as c2
WHERE c1.minutes_online_month > c2.yourScore

[jaymc]

fenway strikes again

 

Thanks, works perfect.

[fenway]

fenway strikes again

 

Thanks, works perfect.

No problem.. don't know what I was thinking before; inner join before FROM? I need more sleep ;-)