Danny Danny - 1 year ago 72
SQL Question

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.

Answer Source

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)