CODEWITHSUNDEEP
mysqlsql
DemCodeLines
DemCodeLines

Reputation: 1920

Get highest value of 2 columns and the full row information of the row that has the highest number

id  |  name  |  num1  |  num2
 0  | Johnny |    0   |   7
 1  | Jason  |   50   |   3
 2  | John   |   60   |   1
 3  | Tom    |    5   |   70

If I run the following query, I get the following result, as I should: SELECT MAX(GREATEST(num1, num2)) FROM data

What I need to get, however, is the full information from the row.

So since I got 70, I want to be able to access num1, name and id of that row.

Is this possible at all?

I did the following, SELECT * FROM data WHERE num1 = MAX(GREATEST(num1, num2)) OR num2 = MAX(GREATEST(num1, num2)); and got an error saying, "Invalid use of group function."

Upvotes: 0

Views: 43

Answers (1)

barry-johnson
barry-johnson

Reputation: 3224

Am I missing something. Why wouldn't you just?

select * from data order by GREATEST(num1,num2) desc limit 0,1;

If there is a tie you will not have guaranteed repeatable behavior if you only order on greatest() of the two numbers. What if Smith's num1 is 70 and Jones' num2 is 70? Either one could come up as first each time the query is executed. If you want to have a repeatable selection, add another sort column that will guarantee a predictable ordering (for example, sorting on the primary key).

Upvotes: 4

Related Questions