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

Question

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

I'm thinking UNION. Something like SELECT Col1 AS 'a' FROM mytable UNION SELECT Col2 AS 'a' FROM mytable UNION ....... ORDER BY 'a' LIMIT 10; SQLFiddle keeps dieing on my so haven't got a test — David K-J, Sep 16 '16 at 15:16
@DavidK-J Try [**rextester**](http://rextester.com/l/mysql_online_compiler) — Juan Carlos Oropeza, Sep 16 '16 at 15:18
@JuanCarlosOropeza lol.. It just breaks faster and more verbosely — David K-J, Sep 16 '16 at 15:22
@DavidK-J Well first time that happen to me. But I use rextester more for Sql Server and Oracle, and use sqlFiddle to `TEXT to DDL` — Juan Carlos Oropeza, Sep 16 '16 at 15:32

Juan Carlos Oropeza · Accepted Answer · 2016-09-16T16:07:06.800

3

 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

edited Sep 16 '16 at 16:07

answered Sep 16 '16 at 15:17

Juan Carlos Oropeza

47,252
12
78
118

1

Someone agrees :) – David K-J Sep 16 '16 at 15:18
@DavidK-J Yes, but im not sure if you can just put the`ORDER BY` at the end like you suggest, so I use the subquery syntaxis to be sure. – Juan Carlos Oropeza Sep 16 '16 at 15:20
@JuanCarlosOropeza I'm almost certain that you can :-) – Kamil Gosciminski Sep 16 '16 at 15:21
1

@KamilG. You are right. I update to Include that solution. – Juan Carlos Oropeza Sep 16 '16 at 15:27
@JuanCarlosOropeza Thank you for the answer, but just realized my question changed a bit so I have reopened it up :) – ocean800 Sep 16 '16 at 15:49
Now I dont understand the problem. Did you update the sample data? How you get 18? and what is the result you want? – Juan Carlos Oropeza Sep 16 '16 at 16:05
@JuanCarlosOropeza Thanks!!! Just realized what a simple mistake it was to call MAX(ABS()) when I was already ordering them at the end :) – ocean800 Sep 16 '16 at 16:16

score 1 · Answer 2 · answered Sep 16 '16 at 15:18

For every column get their 10 greatest values and retrieve 10 top values from all of them:

select max_value
from (
  (select col1 AS max_value
  from yourtable
  order by col1 desc
  limit 10)
  union all
  (select col2
  from yourtable
  order by col2 desc
  limit 10)
  union all
  (select col3
  from yourtable
  order by col3 desc
  limit 10)
  union all
  (select col4
  from yourtable
  order by col4 desc
  limit 10)
  ) foo
order by max_value desc
limit 10

score 1 · Answer 3 · answered Sep 16 '16 at 15:28

1

select * from ( 
select Col1 as data from database1 
UNION 
select Col2 as data from database1 
UNION 
select Col3 as data from database1 
UNION 
select Col4 as data from database1 
) as T order by data desc ;

The union command is really helpful in this scenerio

answered Sep 16 '16 at 15:28

Master Yoda

531
8
22

1

so how is your answer different from mine? Also you need `UNION ALL` other wise you will remove duplicated values. – Juan Carlos Oropeza Sep 16 '16 at 15:39

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

3 Answers3