CODEWITHSUNDEEP
sql
Travis J
Travis J

Reputation: 109

How to rotate rows in sql

I have a table that has two cols REC_NO (INT), and ORDER_NO (INT). Structured like below

REC_NO || ORDER_NO
1 || 1
2 || 2
3 || 3

There can any number of rows in the table what I need to do is rotate the ORDER_NO so that 1 becomes 3, 2 becomes 1, and 3 becomes 2. I have done this using a hard coded case statement, but need some help making it more flexible. See the code below for what I have that does work for the limited set like above:

UPDATE ROTATION_LIST SET ORDER_NUM = 
CASE 
WHEN ORDER_NUM = 1 THEN 3 
WHEN ORDER_NUM = 2 THEN 1 
WHEN ORDER_NUM = 3 THEN 2 
END

This is not a homework assignment or brain teaser I have an actual need for this.

Upvotes: 2

Views: 300

Answers (4)

Conrad Frix
Conrad Frix

Reputation: 52645

If your RDMS supports LAG you could use it like so

WITH new_order 
     AS (SELECT rec_no, 
                COALESCE(Lag(order_no) OVER ( ORDER BY order_no),  --Previous value
                         Max(order_no) OVER ( ORDER BY order_no DESC)) --Max Value when no previous
                   NEW_ORDER_NO 
         FROM   rotation_list) 
UPDATE rotation_list 
SET    order_no = new_order.new_order_no 
FROM   rotation_list 
       INNER JOIN new_order 
               ON rotation_list.rec_no = new_order.rec_no

DEMO

If your RDMS support ROW_NUMBER but not LAG() (looking at you sql 2008) you can do this

WITH cte 
     AS (SELECT rec_no, 
                order_no, 
                Row_number() OVER ( ORDER BY order_no) rn 
         FROM   rotation_list), 
     new_order 
     AS (SELECT no.rec_no, 
                COALESCE(no_prev.order_no, 
                         Max(no.order_no) OVER (partition BY NULL )) new_order_no 
         FROM   cte no 
                LEFT JOIN cte no_prev 
                       ON no.rn - 1 = no_prev.rn) 
UPDATE rotation_list 
SET    order_no = new_order.new_order_no 
FROM   rotation_list 
       INNER JOIN new_order 
               ON rotation_list.rec_no = new_order.rec_no;

DEMO

Upvotes: 2

Glenn
Glenn

Reputation: 9150

And yet another, with Oracle syntax. Change the +1 to the the amount of rotation desired:

SELECT t.REC_NO, t.ORDER_NO
  from myTable t
  CROSS JOIN (SELECT COUNT(*) AS recordCount FROM myTable) s
  ORDER BY MOD(rownum + 1, s.recordCount)

Upvotes: 0

Anon
Anon

Reputation: 10908

UPDATE ROTATION_LIST
SET ORDER_NUM = 1 + ORDER_NUM % (SELECT MAX(ORDER_NUM) FROM ROTATION_LIST)

Upvotes: 0

JB King
JB King

Reputation: 11910

Is it that you want to set the value for row 1 to be the # of rows and all the other rows is simply one less than the current value? Seems like a couple of queries would be my suggestion. Update the row with the number - 1 first, and then fix the value for one by taking the count of the order numbers.

Alternatively, you could set the value for 1 to be the max order number + 1, and then run the query to decrement all the values by one to get it to the right spot.

Upvotes: 1

Related Questions