How to find the column with maximum value within a single row with multiple columns?

Question

I have a temporary table as follows:

|----------|------------|-------------|--------|-----------|
| Country  | Confirmed  | Unconfirmed | Deaths | Recovered |
|----------|------------|-------------|--------|-----------|
|     A    |   95782    |   1034219   |  6723  |   25892   |
|----------|------------|-------------|--------|-----------|

I need to find the column_name (among the four given columns) with maximum value. Let's say I need to find whether confirmed cases or unconfirmed or deaths or recovered are more for country A.

In this case the expected output is "unconfirmed" because it has the maximum value among the four given columns.

If you use temporary table, try create table with transposed columns. With simple structure: `Country, Type_of_result, Result`. You may work with group function for summarize or order any result combinations. — Pavel Musil, Apr 18 '20 at 14:38

nbk · Answer 1 · 2020-04-18T15:28:55.423

Use GREATEST

SELECT Country, 'unconfirmed', GREATEST(Confirmed, Unconfirmed, Deaths, Recovered)
FROM TAble1;

A little more content

Your complete Query looks like this

SELECT
t1.country
, ( case 
        WHEN t1.Confirmed = t2.maxnumber Then 'Confirmed'
        WHEN t1.Unconfirmed = t2.maxnumber Then 'Unconfirmed'
        WHEN t1.Deaths = t2.maxnumber Then 'Deaths'
        WHEN t1.Recovered = t2.maxnumber Then 'Recovered'
        ELSE 'unknown'
    END) type
,t2.maxnumber
FROM table1 t1 inner join
(SELECT 
Country
,GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) maxnumber
FROM table1) t2 ON t1.country = t2.country

Example

Schema (MySQL v5.7)

CREATE TABLE table1 (
  `Country` VARCHAR(1),
  `Confirmed` INTEGER,
  `Unconfirmed` INTEGER,
  `Deaths` INTEGER,
  `Recovered` INTEGER
);

INSERT INTO table1
  (`Country`, `Confirmed`, `Unconfirmed`, `Deaths`, `Recovered`)
VALUES
  ('A', '95782', '1034219', '6723', '25892'),
  ('B', '95782', '1034219', '6723', '2225892');

Query #1

SELECT
t1.country
, ( case 
        WHEN t1.Confirmed = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Confirmed'
        WHEN t1.Unconfirmed = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Unconfirmed'
        WHEN t1.Deaths = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Deaths'
        WHEN t1.Recovered = GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) Then 'Recovered'
        ELSE 'unknown'
    END) type
,GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered )
FROM table1 t1;

| country | type        | GREATEST(Confirmed  , Unconfirmed , Deaths , Recovered ) |
| ------- | ----------- | -------------------------------------------------------- |
| A       | Unconfirmed | 1034219                                                  |
| B       | Recovered   | 2225892                                                  |

View on DB Fiddle

GREATEST actually works, but it returns only the value of the respective column i.e. 1034219. I want the column name as output, which is "unconfirmed". — learningsql, Apr 18 '20 at 15:08
So added the complete Query, i added one more row to show that ot worls — nbk, Apr 18 '20 at 15:29

score 1 · Answer 2 · answered Apr 18 '20 at 14:39

One option is to unpivot and use row_number() to identify the column with most cases for each country:

select country, case_type, nb_cases
from (
    select 
        t.*, row_number() over(partition by country order by nb_cases desc) rn
    from (
        select country, 'confirmed' case_type, confirmed nb_cases from mytable
        union all
        select country, 'unconfirmed', unconfirmed from mytable
        union all
        select country, 'deaths', deaths from mytable
        union all
        select country, 'recovered', recovered from mytable
    ) t
) t
where rn = 1

Demo on DB Fiddle:

| country | case_type   | nb_cases |
| ------- | ----------- | -------- |
| A       | unconfirmed | 1034219  |

score 0 · Answer 3 · answered Apr 18 '20 at 15:28

You can use greatest() and case:

select t.*,
       (case greatest(Confirmed, Unconfirmed, Deaths, Recovered)
             when Confirmed then 'Confirmed'
             when Unconfirmed then 'Unconfirmed'
             when Deaths then 'Deaths'
             when Recovered then 'Recovered'
       end) as column_greatest
from t;

Two notes.

First, this does not work if any of the columns are NULL. You have no sample sample suggesting that this is the case. But given that the values are never negative, you could use COALESCE() if NULL values might be present.

Second, if there are ties, this returns the first column with the maximum value. Once again, you don't specify what to do, so this seems like a reasonable interpretation of the question.

How to find the column with maximum value within a single row with multiple columns?

3 Answers3