SQL order by absolute values

Question

I have some data in my table below

table_1
     column_1     column_2
        1           10
        1           20
        1           30
        1           40
        1           50
        2           -10
        2           -20
        2           -30
        2           -40
        2           -50

I want to change this result to something like this

 column_1     column_2
    1           10
    2           -10
    1           20
    2           -20
    1           30
    2           -30
    1           40
    2           -40
    1           50
    2           -50

I am not sure if there is a way to do this using order by? What I am trying to show is I am trying to show (10,-10) as a group of data

Is it a requirement that, in case of ties of the absolute value, positive comes before negative? — Jeroen Mostert, Jun 23 '16 at 11:08
@JeroenMostert, yeah you can look at it this way, I want to do it as (10, -10) as a group of data — RedRocket, Jun 23 '16 at 11:09
The result set is just a bunch of rows, not a group of any kind. The order in which you return them isn't "grouping". If you want that, you could just do it on the client side, or you want a more complicated query that does group things somehow ([with `GROUP BY` and some concatenation](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server)). — Jeroen Mostert, Jun 23 '16 at 11:10

sagi · Accepted Answer · 2016-06-23T11:29:02.000

13

You can simply use ABS() function, that returns the absolute value of a number :

SELECT * FROM YourTable
ORDER BY ABS(Column_2),column_2 desc

This query will be ordered by the absolute value of Column_2

edited Jun 23 '16 at 11:29

answered Jun 23 '16 at 11:07

sagi

40,026
6
59
84

score 11 · Answer 2 · answered Jun 23 '16 at 11:08

11

SELECT * 
FROM <table>
ORDER BY abs(column_2), column_2 desc

answered Jun 23 '16 at 11:08

t-clausen.dk

43,517
12
59
92

score 2 · Answer 3 · answered Jun 23 '16 at 11:07

2

Looks like you want to sort on the absolute value of the second column, then the first column.

select column_1, column_2 from table_1 order by abs(column_2),column_1

answered Jun 23 '16 at 11:07

alroc

27,574
6
51
97

SQL order by absolute values

3 Answers3