sql: highest value, comparing fields
 

I want an sql query that shows me the field with the highest value. I have a table like this:

uhost varchar(50)
nick varchar(30)
val1 int
val2 int
val3 int
...
val8 int

Now, I need a query that shows the nick, the total of all values and the highest of the different fields. I have already fetched the total by adding them all (val1 + val2 + val3), maybe there is a better way?

Regards.

What database are you using?
Try this:
select nick, max(val1, val2, val3...) as maxvalue, (val1+val2val3...) as sumvalues
from...

I must point out that when you have to face this kind of problem it is usually a result of poor database design. Keeping the valX-values in a separate table would probably be better. But them again - I have no idea what these values represent so who am I to judge!

Edit: Hej föresten :-)

No, max seems to only be designed to take one field into it and return the post with the highest value of that field, not what I am looking for.

I think GREATEST should work:
GREATEST(val1,val2,val3) as greatest

I agree with rokka - that's a design-flaw.
The only way around this will be programmatical,
you will need to script a solution or build the
table(s) differently.

[edit]
GREATEST is not in the SQL standards, and the only
database I know of to use it is oracle ;}
[/edit]



Cheers,
Tink

And MySQL it would seem ;)
http://dev.mysql.com/doc/mysql/en/co...operators.html

I didn't realise it wasn't part of the standards though so it may not be of any use.

I am using MySQL.

I am aware of that the design is kind of poor but I have been trying to think of any other way to sort things out but every other solution would create other problems. But since the table is not that big really it is not a big cost to have it as it is right now, it is not a table that will grow much either. So I guess it is ok by now.

Hallå, där. :)