Return the highest value between columns and the ID for the line of this higher value

Question

Return the highest value between columns and the ID for the line of this higher value

Navigation

#1 by (1 votes)
#2 by (1 votes)

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'd like to also return the ID for the line that has the highest value. I'm doing 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 the ID of the row that has the largest value, but the ID of the first row of the database.

Then how do I make the select to return the highest value among the columns and the ID for the row of that highest value

mysql sql select

asked by anonymous 12.11.2018 / 22:28

2 answers

1

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

The above code selects id and valor , setting it to sort from highest to lowest value, but is limited to one record.

13.11.2018 / 04:41

Are variables allocated randomly in memory? Bring id on route

score 1 · Accepted Answer

I can even think of a solution but it would be gambiarra, I do not know if it works (by using union it will not perform as well 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've created a fiddle to validate, too, if you like.