CODEWITHSUNDEEP
sqlt-sql
Ish
Ish

Reputation: 671

SQL Rearrange row order

Problem: A row in my_table contains

1
2
3
3
4
4
4

I want to rearrange the row order to the following

4
4
4    
1
3
3
2

Is there any way I can perform this?

I have not written any code so far. ( I do not know how to begin)

Upvotes: 1

Views: 3571

Answers (4)

Adam Wenger
Adam Wenger

Reputation: 17540

SELECT yourColumn
FROM
(
   SELECT yourColumn
      , CASE yourColumn
           WHEN 4 THEN 1
           WHEN 1 THEN 2
           WHEN 3 THEN 3
           WHEN 2 THEN 4
        END AS SortColumn
   FROM yourTable
) AS t
ORDER BY t.SortColumn ASC

This will sort 4, 1, 3, 2

Upvotes: 0

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112259

Create a lookup table with the original value and the corresponding sort order:

SortLookupTable:
OriginalValue, SortOrder
1,             2
2,             4
3,             3
4,             1

Then join the table to your original table

SELECT T.*
FROM
    MyTable T
    INNER JOIN SortLookupTable S
        ON T.Value = S.OriginalValue
ORDER BY
    S.SortOrder;

Upvotes: 0

JNK
JNK

Reputation: 65147

Just use CASE in the ORDER BY - it's much cleaner and easier to read.

...
ORDER BY CASE WHEN YourColumn = 4 then 0
              WHEN YourColumn = 2 then 1
              ELSE 2 END, yourcolumn

It's also a bad idea to change the order of the rows since there is actually no inherent order in SQL - it's all in the presentation layer, so use ORDER BY in your SELECTs to accomplish it.

Upvotes: 2

user894932
user894932

Reputation:

as Adam Wenger said,

or if you wanted, it could be done in your table, meaning your sql statement can remain as a basic select * from table order by newcolumn*

*see below

add a new column
update each row set column=1 where original_column=4
update each row set column=2 where original_column=2
update each row set column=3 where original_column=1
update each row set column=4 where original_column=3

and then order by this new column.

Upvotes: 2

Related Questions