ocean800 - 1 month ago 6x

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

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

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?

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

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

Source (Stackoverflow)

Comments