Jump to content

one query for selecting max numbers


asmith

Recommended Posts

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

 

 

Link to comment
https://forums.phpfreaks.com/topic/113610-one-query-for-selecting-max-numbers/
Share on other sites

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,

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.