CODEWITHSUNDEEP
mysqlmax
TrtG
TrtG

Reputation: 2848

MySql select max value of a pair of column in one query

I need a query to select a row based on a max pair of columns. In the following table, how would you select the rows which has the highest VALUE1 and in this first results set, the one which has the highest VALUE2?

+----+---------+---------+
| ID | VALUE1  | VALUE2  |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       2 |       1 |
|  3 |       5 |       4 |
|  4 |       4 |       1 |
|  5 |       4 |       3 |
|  6 |       4 |       5 |
|  7 |       5 |       1 |
|  8 |       4 |       2 |
|  9 |       4 |       6 |
| 10 |       4 |       8 |
| 11 |       5 |       3 |

In this case, the row with ID = 3 should be returned.

Upvotes: 1

Views: 2091

Answers (2)

Ankit Sharma
Ankit Sharma

Reputation: 4069

Limit is the best option here but if you still want some other method then you can use sub query 

SELECT * FROM tableName  where (Value1 + Value2) = (select max((Value1 + Value2))
from tableName)

Upvotes: 0

Mchl
Mchl

Reputation: 62395

SELECT * FROM tableName ORDER BY Value1 DESC, Value2 DESC LIMIT 1

Upvotes: 8

Related Questions