selecting the highest value accross 4 fields

[EchoFool]

Hey,

 

 

I want to find the highest Total of 4 fields when added together that is in the same group of rows which in this situation is based on level. In simpler words im trying to find who is the strongest for a particular level.

 

I so far written this for my query but need some help in the blank section:

 

$Get = mysql_query("SELECT (strength+defence+speed+agility) As Total 
FROM userstable WHERE Level = ($Level+5 OR (blank bit here) ) ORDER BY Total DESC Limit 1")

 

The complicated issue with this is the "Blank bit here" part... if there is no user that is 5 levels higher i need it to find the "next highest" level from that so say i was level 1 and there was no level 6 users....and the next highest level of users from that was level 8s the query needs to select the level 8 users....is there a way that can be done in one query?

[paul2463]

you mean this??

 

$Get = mysql_query("SELECT (strength+defence+speed+agility) As Total 
FROM userstable WHERE Level >= $Level+5  ORDER BY Total DESC Limit 1")

 

so it will pick rows that have a level that is greater than or equal to $level+5

 

you will probably have to "order by level Desc" so you get the lowest level that is higher than $level+5 if that makes sense

[EchoFool]

you mean this??

 

$Get = mysql_query("SELECT (strength+defence+speed+agility) As Total 
FROM userstable WHERE Level >= $Level+5  ORDER BY Total DESC Limit 1")

 

so it will pick rows that have a level that is greater than or equal to $level+5

 

you will probably have to "order by level Desc" so you get the lowest level that is higher than $level+5 if that makes sense

 

True but would that select the "strongest user" out of the level by having:

 

ORDER BY Level DESC, Total DESC Limit 1

 

?

[Barand]

strongest for each level given by

SELECT level, MAX(strength+defence+speed+agility) As Total
FROM userstable
GROUP BY level

[EchoFool]

strongest for each level given by

SELECT level, MAX(strength+defence+speed+agility) As Total
FROM userstable
GROUP BY level

 

I will give it a go thankyou.