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

```