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!!

`````` 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
``````