Return the highest value between columns and the row ID of that highest value

Asked

Viewed 255 times

5

Let’s say I have two columns in my table, column A and B.

+-----+-----+-----+
|  ID |  A  |  B  |
+-----+-----+-----+
|  1  | 500 | 681 |
+-----+-----+-----+
|  2  | 980 | 101 |
+-----+-----+-----+
|  3  | 110 | 981 |
+-----+-----+-----+

If I do the following select, I can return the highest value

select GREATEST(MAX(A), MAX(B)) as bigger FROM valores

In the above example the value 981 returns from select. However I would also like to return the ID for the line that has the highest value. I am making the following select:

select valor.*, GREATEST(MAX(A), MAX(B)) as bigger FROM valores as valor

It still returns me the value 981, but it does not return me the ID of the row that has the highest value, but the ID of the first row of the database.

Then how to select it to return the highest value between the columns and the row ID of that highest value

mysql sql database select

2 answers

1


I can even think of a solution but it would be gambiarra, I do not know if answers (by using union will not have such a good performance depending on the amount of data):

SELECT id, val FROM
(SELECT id, a AS val FROM valores
UNION ALL
SELECT id, b AS val FROM valores) tmp_valores
ORDER BY val desc
LIMIT 1;

I created a fiddle also to validate, if you want.

  • It may be gambiarra as you said, but I confess that I do not understand much of select more elaborate, however your solution did exactly what was needed.

1

SELECT campoid,campovalor
FROM TABELA
ORDER BY campovalor ASC
LIMIT 1

the code above, select id and valor, defining that it will sort from the highest to the lowest value, but is limited to a record.

Browser other questions tagged mysql sql database select

You are not signed in. Login or sign up in order to post.