CODEWITHSUNDEEP
mysql
user1022585
user1022585

Reputation: 13651

mysql get highest value with column name

I have a table called myTable like this:

Name    type1    type2    type3
----------------------------------------
John    3        6        9
Craig   5        2        24

I want to get whichever column has the highest for 'John'- that is, the column name, and the amount.

I can't think of a solution to handle it, any pointers?

Upvotes: 0

Views: 1682

Answers (2)

Satyendra Kumar Singh
Satyendra Kumar Singh

Reputation: 46

Use the query below to find highest value column using name:

SELECT Name, @highest_val:= GREATEST(type1, type2, type3) AS highest_col_value,
   CASE @highest_val WHEN type1 THEN 'type1'
                     WHEN type2 THEN 'type2'
                     WHEN type3 THEN 'type3'                         
   END AS highest_value_column_name
FROM myTable
WHERE Name = 'John';

If you want to retrieve all records then use the query below:

SELECT Name, @highest_val:= GREATEST(type1, type2, type3) AS highest_col_value,
   CASE @highest_val WHEN type1 THEN 'type1'
                     WHEN type2 THEN 'type2'
                     WHEN type3 THEN 'type3'                         
   END AS highest_value_column_name
FROM myTable;

Here GREATEST() MySql function finds greatest value for given columns as highest_col_value and CASE will match with each column to find the column name as highest_value_column_name.

Upvotes: 2

Sloan Thrasher
Sloan Thrasher

Reputation: 5040

There's no function that returns the column name based on some condition, but you can use the IF function. To get the largest value from a list, use the GREATEST function.

SELECT
    `Name`,
    IF(`type1` > `type2` AND `type1` > `type3`,'type1',
        IF(`type2` > `type1` AND `type2` > `type3`,'type2',
            IF(`type3` > `type1` AND `type3` > `type2`,'type3','N/A'
            )
        )
    ) as `maxColName`,
    GREATEST(`type1`,`type2`,`type3`) as `maxValue`
FROM `myTaable`

Upvotes: 0

Related Questions