ocean800 ocean800 - 2 months ago 10
MySQL Question

MySQL - Find the "Max" Absolute Value of 10 records across multiple columns?

I have a table that looks something like:

Country | Item | Col1 | Col2 | Col3 | Col4
4 | 4 | .152 | .01 | .65 | 1
9 | 6 | .145 | .98 | .469 | .001
56 | 7 | .001 | .987 | .011 | .223
78 | 2 | -18 | .269 | -.70 | .1


And so on. I want to find the top 10 max ABS(entries) from
Col1
,
Col2
,
Col3
,
Col4
. So in this case, the answer would be:

-18
1
.987
.98
-.70
.65
.469
.223
....


And so on. But how would I do this? I came across both this which details how to find the max absolute value n columns based on only a single coumn, and also this but that only finds the max of the row per each row. How can I combine the two?

EDIT

As suggested in an answer, I tried something like this:

SELECT MAX(ABS(`Col1`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col2`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col3`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col4`)) as `absValue` FROM Table1
ORDER BY `absValue` DESC
LIMIT 10


But got:

18
1
0.9869999885559082
0.699999988079071


What am I doing wrong? SQL Fiddle

Any help would be greatly appreciated, thanks!!

Answer
 SELECT  `value` 
 FROM (
           SELECT col1 as `value`  FROM yourTable
           UNION all
           SELECT col2 as `value`  FROM yourTable
           UNION all
           SELECT col3 as `value`  FROM yourTable
           UNION all
           SELECT col4 as `value`  FROM yourTable
      ) T
ORDER BY `value` DESC
LIMIT 10

As David and Kamil said, you dont need the subquery

SQL DEMO

SELECT col1 as `value`  FROM Table1
UNION all
SELECT col2 as `value`  FROM Table1
UNION all
SELECT col3 as `value`  FROM Table1
UNION all
SELECT col4 as `value`  FROM Table1
ORDER BY `value` DESC
LIMIT 10

After QUESTION EDIT try

SELECT ABS(`Col1`) as `absValue` FROM Table1
UNION ALL 
SELECT ABS(`Col2`) as `absValue` FROM Table1 
UNION ALL 
SELECT ABS(`Col3`) as `absValue` FROM Table1
UNION ALL 
SELECT ABS(`Col4`) as `absValue` FROM Table1
ORDER BY `absValue` DESC
LIMIT 10 

OR to get the exact output

SELECT `Col1` as `value` FROM Table1
UNION ALL 
SELECT `Col2` as `value` FROM Table1 
UNION ALL 
SELECT `Col3` as `value` FROM Table1
UNION ALL 
SELECT `Col4` as `value` FROM Table1
ORDER BY ABS(`value`) DESC
LIMIT 10 
Comments