CODEWITHSUNDEEP
mysqlsqlselectmax
Danny
Danny

Reputation: 131

Show column by max() other columns

I have a database table with 3 columns: id, number1, number2. id contains 1,2,3...n and number1, number2 contains regular int numbers.

I want to show in the result the id column which will show the max value from number1*number2.

I wrote this: 

select id, max(number1*number2) from numbers;

But in the result it shows me the id and number1*number2 columns.

I want to show only id in the result.

Upvotes: 3

Views: 690

Answers (2)

Mureinik
Mureinik

Reputation: 312056

You could use an inner query to get the multiplication and then use the outer one to get the entire row:

SELECT id
FROM   mytable
WHERE  (number1 * number2) = (SELECT MAX(number1 * number2) FROM mytable)

Upvotes: 3

Iłya Bursov
Iłya Bursov

Reputation: 24199

you can try something like that:

select id
from numbers
order by number1*number2 desc
limit 1

Upvotes: 2

Related Questions