Mysql MAX()

[wongerlt]

Hi all.

I have query

SELECT name,game,score, MAX(score) FROM `servers` WHERE `on`='2' group BY `game` DESC

but it bad ;/

I need:

Example:

id - name - game - score

1 - test - 45 - 78

2. - iks - 13 - 43

3. - lips - 45 - 15

4. - cat - 45 - 74

5. - dog - 13 - 56

...

and i need query a mains so:

1.test 78

2.dog 56

 

one rezult from one game with max score.

 

Soz for bad english.

[fugix]

what are the results that you are receiving now?

[PFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.1/en/example-maximum-column-group-row.html

[wongerlt]

Thx.

But what here wrong:

SELECT ids, game, score
FROM   shop s1
WHERE  score=(SELECT MAX(s2.score)
              FROM shop s2
              WHERE s1.ids = s2.ids) GROUP BY `game`;

table:

ids.      game    score

4568 12 2056

4646 12 3

454564 13 75

8778 13 16

46487 13 1

4541 24 56

13131 24 87

6464 24 185

 

i get result:

ids.    game.  score.

4541 24 56

454564 13 75

4568 12 2056

 

but should be:

ids.      game.  score.

4568 12 2056

454564 13 75

6464 24 185

 

why last result is bad?

[PFMaBiSmAd]

You don't use GROUP BY when you do it that way.

[wongerlt]

ok , but now show all rows ;/

not group by game

[PFMaBiSmAd]

The where clause should match the game values -

 

WHERE s1.game = s2.game

[wongerlt]

Very thx.