Question: 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.

Answer: 
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](http://sqlfiddle.com/#!6/90b53/14)

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](http://sqlfiddle.com/#!3/90b53/37)
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)

```