one query for selecting max numbers

[asmith]

hey guys

 

table : 

ID    num1   num2   num3

1     34       457     245
2     534     324    3244
3     1        632      123

 

Is it possible by one query i select the ID content where it is max in num1 and num2 and num3  ? 

 

Example :   

 

select id from test where num1 = (select max(num1) from table)

select id from test where num2 = (select max(num2) from table)

select id from test where num3 = (select max(num3) from table)

 

so that i mae those 3 queries into one. The important thing to me is i could use "mysql_num_rows" on the result of the query, which if  2 users have same max number in num1 i could find out.  (using limit in query won't let me find out if there's more than one) 

 

 

thanks in advance

 

 

[cooldude832]

just a guess but might work if written right (don't know if i did )

SELECT IF(MAX(num1),ID) as ID1, IF(MAX(num2),ID) as ID2, if(MAX(num3),ID) as ID3) from `test`

 

[asmith]

not working. (though i don't know how to write that right I'll search to find something similar)

[bluejay002]

you may use union:

 

select id, max(num1) as highest, 'option1' as options from table
union
select id, max(num2) as highest, 'option2' as options from table
union
select id, max(num3) as highest, 'option3' as options from table

 

the reason i placed id is so that you can trace back which ID has it. also, i added options so that you can always retrieve the correct data, even if it get mixed up, say, no result for the second selection.

 

Jay,