[SOLVED] SQL Query MAX

[Matrixkid]

Hi there,

 

I am having trouble getting the results I want from a mysql query.

 

I have this data:

 

Basically from this data I want the highest value in the 2nd column, so my desired results would be:

Elite Madac | 427 | 100

Elite Sabre  | 512 | 110

 

Instead, I get this:

 

This is my sql query:

SELECT idName,max(idAttacks),max(idPoints) FROM stats GROUP BY idName ORDER BY max(idPoints) DESC

 

Thanks for the help!

Cheers

[Fadion]

Try this query:

 

SELECT idName, MAX(idAttacks) as maxAttacks, MAX(idPoints) as maxPoints FROM stats GROUP BY idName ORDER BY maxPoints DESC

[Matrixkid]

its still giving me the lower numbers 

 

 

[Fadion]

Thinking it well, you should select only one MAX field. Try this and if it doesn't work, post the table with some data and the column names.

 

SELECT idName, idAttacks, MAX(idPoints) as maxPoints FROM stats GROUP BY idName ORDER BY maxPoints DESC

[Matrixkid]

yeah, that didnt work either. lol. 

 

Some notes on the table - the query works for all the data except for Elite Mamac, and Elite Sabretooth.

Here is the table:

 

here is the result from the last suggest query:

 

 

I tried removing the data from Mamac and Sabretooth fields and re-enter them into the DB, and still the problem persists.

But the queries do work for the rest of the data - i am just really confused as to why it isnt working for these two specific ones.

 

Thanks for your help.

Cheers

[Fadion]

That's weird. The query should work there. Select max points/attacks, group the names and order by max points. Nothing special here, so that's really weird. Two quick questions. Are idPoints and idAttacks set as integers in mysql (especially on those 2 guys)? Why do you have repeating records? Generally, it's not a good practice repeating records. You would have to update, delete and insert two fields every time.

[Matrixkid]

everyday i update the table with the new stats, and i need past stats so i can track the users. is there another way to accomplish this?

 

 

...and...hahaha. it was the integer thing. totally forgot to set the field type. thanks for that pointer. really appreciate your help! +1

 

[Fadion]

Glad it helped.

 

As for the stats, you can expand it even more and have full stats for the users. Have a table stats, with the userid, attacks, points and date and keep everyday records. So you can generate stats not only for the last day, but for the whole registration time (or maybe limit it to 1 month or something). It could need a simple "admin panel" where you can add new stats and old ones are moved automatically to the stats table. Anyway that's you choice

 

Cheers.