How to find the maximum multiplication of two columns in SQL

Question

I have a table which contains two columns.

I want to find the maximum multiplication of these two columns and how many of them have this maxmimum value?

I have tried the max of the multiplication of two columns

But it didn't work. Any help will be appreciated.

What do you mean with `max of the multiplication of two columns`? Is it the value contained inside the column? In this case, please provide an example to clarify your issue, otherwise it is very hard to understand what you need. — Dos, Jun 12 '20 at 13:56

score 0 · Answer 1 · answered Jun 12 '20 at 10:58

0

You can use max():

select max(col1 * col2)
from t;

To find the number that match:

select count(*)
from t
where (t.col1 * t.col2) = (select max(t2.col1 * t2.col2) from t t2);

You can also use window functions:

select count(*)
from (select t.*, 
             rank() over (order by col1 * col2 desc) as seqnum
      from t
     ) t
where seqnum = 1;

answered Jun 12 '20 at 10:58

Gordon Linoff

I'm using MySQL and it gives me an error about select count(*). Is there a way to solve thsi? – Ellie Jun 12 '20 at 11:08
@Ellie . . . MySQL supports `count(*)`. – Gordon Linoff Jun 12 '20 at 12:41

1 Answers1