CODEWITHSUNDEEP
mysql
yuehust
yuehust

Reputation: 23

Get the maximum value in a row

For the following table in MySQL (Prices in the table means the prices provide by different suppliers)

Products   | Price1 | Price2 | Price3
Apple      | 2      | 3      | 4
Pear       | 2      | 1      | 1.5
Strawberry | 10     | 12     | 11

How can I get the results that the maximun price for Apple is Price3, maximun price for Pear is Price1, maximun price for Strawberry is Price2, for example, I want the results to be retrieved from the table above:

Products     | MaximunPrice
Apple        | Price3
Pear         | Price1
Strawberry   | Price2

Upvotes: 1

Views: 59

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

Use GREATEST:

SELECT Products,
       CASE GREATEST(Price1, Price2, Price3) 
         WHEN Price1 THEN 'Price1'
         WHEN Price2 THEN 'Price2'
         WHEN Price3 THEN 'Price3'
       END AS MaxPrice
FROM TableName

Result:

Products    MaxPrice
---------------------
Apple       Price3
Pear        Price1
Strawberry  Price2

See result in SQL Fiddle

Explanation:

GREATEST() function returns the greatest of the given arguments.

Read more here.

EDIT:

To find the greatest of non-null values, you can use IFNULL or COALESCE:

SELECT Products,
       CASE GREATEST(IFNULL(Price1,0), IFNULL(Price2,0), IFNULL(Price3,0)) 
         WHEN Price1 THEN 'Price1'
         WHEN Price2 THEN 'Price2'
         WHEN Price3 THEN 'Price3'
       END AS MaxPrice
FROM TableName

Result in SQL Fiddle

Upvotes: 7

Related Questions