CODEWITHSUNDEEP
mysql
Qiao
Qiao

Reputation: 17049

Biggest value from two or more fields

I need to get the biggest value from two fields:

SELECT MAX(field1), MAX(field2)

Now, how can I get biggest value from these two?

Upvotes: 100

Views: 87293

Answers (5)

Deepak
Deepak

Reputation: 707

Use of GREATEST/LEAST with MIN/MAX

GREATEST/LEAST: used with the columns, when you want to find the max or min value from the various columns.

MIN/MAX: used with the rows, when you want to find the max or min value from the various rows:

Example table:

enter image description here

SELECT GREATEST(col_a, col_b, col_c) FROM temp;

enter image description here

SELECT MIN(GREATEST(col_a, col_b, col_c)) FROM temp; # 3 as output
SELECT MAX(GREATEST(col_a, col_b, col_c)) FROM temp; # 9 as output


SELECT LEAST(col_a, col_b, col_c) FROM temp;

enter image description here

SELECT MIN(LEAST(col_a, col_b, col_c)) FROM temp; # 1 as output
SELECT MAX(LEAST(col_a, col_b, col_c)) FROM temp; # 7 as output

Upvotes: 4

DRapp
DRapp

Reputation: 48139

SELECT max( CASE
                WHEN field1 > field2 THEN field1
                ELSE field2
            END ) as biggestvalue 
FROM YourTable;

Upvotes: 2

davis
davis

Reputation: 1177

In case you're selecting the GREATEST() for each row

SELECT GREATEST(field1, field2)

It will return NULL if one of the fields is NULL. You could use IFNULL to solve this

SELECT GREATEST(IFNULL(field1, 0), IFNULL(field2, 0))

Upvotes: 49

Ben
Ben

Reputation: 16533

mysql> SELECT GREATEST(2,0);
        -> 2

So, try:

mysql> SELECT GREATEST(MAX(field1), MAX(field2));

Upvotes: 6

Daniel Vassallo
Daniel Vassallo

Reputation: 344271

You may want to use the GREATEST() function:

SELECT GREATEST(field1, field2);

If you want to get the absolute maximum from all the rows, then you may want to use the following:

SELECT GREATEST(MAX(field1), MAX(field2));

Example 1:

SELECT GREATEST(1, 2);
+----------------+
| GREATEST(1, 2) |
+----------------+
|              2 |
+----------------+
1 row in set (0.00 sec)

Example 2:

CREATE TABLE a (a int, b int);

INSERT INTO a VALUES (1, 1);
INSERT INTO a VALUES (2, 1);
INSERT INTO a VALUES (3, 1);
INSERT INTO a VALUES (1, 2);
INSERT INTO a VALUES (1, 4);

SELECT GREATEST(MAX(a), MAX(b)) FROM a;
+--------------------------+
| GREATEST(MAX(a), MAX(b)) |
+--------------------------+
|                        4 |
+--------------------------+
1 row in set (0.02 sec)

Upvotes: 206

Related Questions