mySQL statement - what the #*$!

Anon

Ok, this isn´t really php... however...

I just tried to fetch monthly top-scores from a hiscore table but the statement
returns a wrong name (wrong row) for the score in each month...
why is that ?

date is type datetime

Select name,
max(score), month(date)

as themonth from score group by themonth

any idea ?

andreas

Try this insted:

SELECT name, score, MONTH(date) AS themonth FROM score ORDER BY score DESC limit 0,1

This should return one row with the highest score

Andreas

Anon

hmmm no thats not what I actually wanted 😉

i.e. in 12 months
I want the top player of each month

but it returns the wrong names for the score

if I have

id - name - score

1 - max - 2500 -
2 - joe - 1500 -
3 - ace - 3500 -
4 - jaz - 1000 -

in one month

it returns:
max - 3500 - thedate

but actually should:
ace - 3500 - thedate

I have no clue why it returns the wrong (frist row) name for the highscore...

btanner

I am not going to tell you how to fix this problem, but I can explain where your reasoning has gone awry 🙂

Its a common mistake if you are new to SQL.

When you select:

name, max(score)

You are not saying "Give me top score, and the name associated with that score"

You are actually saying "Give me the first name, and give me the max score"

You see... the records are not ordered by score, so the first name off the top will (probably) not match the max score.

This IS a shortcoming of MySQL. A subselect would probably help you fix this problem easily.

Its early, and I can't think of a good way to do this right now, but I am sure someone who is more awake will reply with a good solution.

Anon

hmm doesn´t look like someone was awake g