Danny Danny - 6 months ago 16
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

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)