14 Replies Latest reply on Oct 5, 2007 4:09 PM by Aketi Jyuuzou

changing the order sequence

user520824 Oct 4, 2007 6:02 PM

Hello,

i have a table test contains these data.

group sequence value
1 1 100
1 2 200
1 3 300
1 4 400
1 5 500
1 6 600

Now i want to change the sequence. ie, eg change the sequence of 2nd record to 5th.
ie moving the 2nd record to 5th and re arrange the sequence. so the records get rearranged
with new sequence.

3 becomes ==> 2
4 becomes ==> 3
5 becomes ==> 4

so the records should looks like

1 1 100
1 2 300
1 3 400
1 4 500
1 5 200 --> 2nd record moved to 5th
1 6 600

how can i achieve this?

1. Re: changing the order sequence

229023 Oct 4, 2007 6:07 PM (in response to user520824)

If it's just an onetime job, just update the values corresponding to that sequence.
Like Show 0 Likes(0)

Actions
2. Re: changing the order sequence

user520824 Oct 4, 2007 6:09 PM (in response to 229023)

No, its not one time job. its a stored procedure which takes the input as current sequence and the new sequence along with its group_id.

exec change_order(group_id, 2,5);
Like Show 0 Likes(0)

Actions
3. Re: changing the order sequence

user520824 Oct 4, 2007 6:10 PM (in response to user520824)

also it can change from any order to a difference sequence.

i can change the sequence of 6 to 2 , 5 to 4 etc....
Like Show 0 Likes(0)

Actions
4. Re: changing the order sequence

74500 Oct 4, 2007 6:24 PM (in response to user520824)

create table test as
select 1 as grp , 1 sq, 100 va from dual union all
select 1, 2, 200 from dual union all
select 1, 3, 300 from dual union all
select 1, 4, 400 from dual union all
select 1, 5, 500 from dual union all
select 1, 6, 600 from dual
/

select
t1.grp,
sq,
decode(t1.sq, 4, (select va from test where sq = 5 ), 3, (select va from test where sq=4),2, (select va from test where sq = 3),
5, (select va from test where sq = 2 ), t1.va) value
from test t1
/

--Result is...

1 1 100
1 2 300
1 3 400
1 4 500
1 5 200
1 6 600
Like Show 0 Likes(0)

Actions

5. Re: changing the order sequence

481618 Oct 4, 2007 6:29 PM (in response to user520824)

WITH t AS (

           SELECT 1 group_id, 1 seq, 100 val FROM DUAL UNION

           SELECT 1 group_id, 2 seq, 200 val FROM DUAL UNION

           SELECT 1 group_id, 3 seq, 300 val FROM DUAL UNION

           SELECT 1 group_id, 4 seq, 400 val FROM DUAL UNION

           SELECT 1 group_id, 5 seq, 500 val FROM DUAL UNION

           SELECT 1 group_id, 6 seq, 600 val FROM DUAL

          )

SELECT

   group_id,

   ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY seq) seq,

   val

FROM

   (

    SELECT

       group_id,

       CASE

          WHEN

             seq = :i

          THEN

             TO_NUMBER(:o)

          WHEN

             seq = :o

          THEN

             TO_NUMBER(:i)

          ELSE

             seq

       END seq,

       val

    FROM

       t

   )



  GROUP_ID        SEQ        VAL

---------- ---------- ----------

         1          1        100

         1          2        500

         1          3        300

         1          4        400

         1          5        200

         1          6        600



6 rows selected.

6. Re: changing the order sequence

Sentinel Oct 4, 2007 7:52 PM (in response to user520824)

update <table> set seq = (

   least(:old,:new)+mod(seq-least(:old,:new)+abs(:old-:new)+2,1+abs(:old-:new)))

where group_id=1 

and (seq between :old and :new or seq between :new and :old);

7. Re: changing the order sequence

481618 Oct 4, 2007 8:36 PM (in response to 481618)

Oops, just noticed you wanted seq 2 to move to place 5 and everything to slide up one, my prior query swapped seq 2 and 5

WITH t AS (

           SELECT 1 group_id, 1 seq, 100 val FROM DUAL UNION

           SELECT 1 group_id, 2 seq, 200 val FROM DUAL UNION

           SELECT 1 group_id, 3 seq, 300 val FROM DUAL UNION

           SELECT 1 group_id, 4 seq, 400 val FROM DUAL UNION

           SELECT 1 group_id, 5 seq, 500 val FROM DUAL UNION

           SELECT 1 group_id, 6 seq, 600 val FROM DUAL

          )

SELECT

   group_id,

   ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY seq) seq,

   val

FROM

   (

    SELECT

       group_id,

       CASE

          WHEN

             seq = :i

          THEN

             TO_NUMBER(:o)

          ELSE

             seq

       END seq,

       val

    FROM

       t

   )



  GROUP_ID        SEQ        VAL

---------- ---------- ----------

         1          1        100

         1          2        300

         1          3        400

         1          4        500

         1          5        200

         1          6        600

8. Re: changing the order sequence

481618 Oct 4, 2007 8:49 PM (in response to Sentinel)

This doesn't move the val column as the user noted so your result ends up like this:

  GROUP_ID        SEQ        VAL

---------- ---------- ----------

         1          1        100

         1          3        200

         1          4        300

         1          5        400

         1          2        500

         1          6        600

This will work though:

UPDATE t SET seq = 

   (

    CASE

       WHEN

          seq = :old

       THEN

          TO_NUMBER(:new)

       WHEN

          seq > TO_NUMBER(:old)

       THEN

          seq - 1

    END

   )

WHERE

   group_id = 1 AND

   (

    seq BETWEEN :old AND :new OR

    seq BETWEEN :new AND :old

   )



4 rows updated.



SELECT * FROM t;



  GROUP_ID        SEQ        VAL

---------- ---------- ----------

         1          1        100

         1          5        200

         1          2        300

         1          3        400

         1          4        500

         1          6        600



6 rows selected.

9. Re: changing the order sequence

Sentinel Oct 4, 2007 10:35 PM (in response to Sentinel)
I missed a term in my calculation. Instead of adding 2 it should have been 1+sign(:old-:new)

update <table>
   set seq =
       (least(:old,:new)+
        mod(seq-least(:old,:new)+abs(:old-:new)+1+sign(:old-:new), 1+abs(:old-:new))
       )
where group_id=1
   and (seq between :old and :new or
        seq between :new and :old);
I had been testing it out with both forward and backward rotations e.g. old and new values of 2, 5 and 5, 2. The last case I had worked with was 5, 2. It now should work properly.
Like Show 0 Likes(0)

Actions
10. Re: changing the order sequence

user520824 Oct 5, 2007 2:21 PM (in response to 481618)

This is great. I use this... but it does not work if i change the priority of 5 to 2...

UPDATE t SET seq =
( CASE WHEN seq = :old
THEN TO_NUMBER(:new)
WHEN seq > TO_NUMBER(:old)
THEN seq - 1 END )

WHERE group_id = 1 AND
( seq BETWEEN :old AND :new OR seq BETWEEN :new AND :old )
Like Show 0 Likes(0)

Actions

11. Re: changing the order sequence

481618 Oct 5, 2007 2:39 PM (in response to user520824)

UPDATE t SET seq = 

   (

    CASE

       WHEN

          seq = :old

       THEN

          TO_NUMBER(:new)

       WHEN

          (

           seq > TO_NUMBER(:old) AND

           seq <= TO_NUMBER(:new)

          )

       THEN

          seq - 1

       WHEN

          (

           seq >= TO_NUMBER(:new) AND

           seq < TO_NUMBER(:old)

          )

       THEN

          seq + 1

    END

   )

WHERE

   group_id = 1 AND

   (

    seq BETWEEN :old AND :new OR

    seq BETWEEN :new AND :old

   )

12. Re: changing the order sequence

Aketi Jyuuzou Oct 5, 2007 3:58 PM (in response to user520824)

create table TestTable AS

SELECT 1 group_id, 1 seq, 100 val FROM DUAL UNION

SELECT 1 group_id, 2 seq, 200 val FROM DUAL UNION

SELECT 1 group_id, 3 seq, 300 val FROM DUAL UNION

SELECT 1 group_id, 4 seq, 400 val FROM DUAL UNION

SELECT 1 group_id, 5 seq, 500 val FROM DUAL UNION

SELECT 1 group_id, 6 seq, 600 val FROM DUAL;

"excahnge 2 and 5" is easy.

select group_id,seq,

case when seq = 2 then max(decode(seq,5,val)) over()

     when seq = 5 then max(decode(seq,2,val)) over()

     else val end as seq

from TestTable;

"excahnge 2 to 5" is this solution.
This solution is used for ReverseSort.

select group_id,seq,val as OldVal,RevSeq - seq as a,abs(RevSeq - seq) as b,

case when seq between 2 and 5

      and RevSeq - seq  > 0 then Lead(val,abs(RevSeq - seq)) over(order by seq)

     when seq between 2 and 5

      and RevSeq - seq <= 0 then Lag (val,abs(RevSeq - seq)) over(order by seq)

else val end as newVal

from (select group_id,seq,val,

      Row_Number() over(order by seq desc) RevSeq

      from TestTable)

order by seq;

update is complex.

update TestTable a set

val = (select newVal

         from (select Row_ID,

                case when seq between 2 and 5

                      and RevSeq - seq  > 0 then Lead(val,abs(RevSeq - seq)) over(order by seq)

                     when seq between 2 and 5

                      and RevSeq - seq <= 0 then Lag (val,abs(RevSeq - seq)) over(order by seq)

                     else val end as newVal

                 from (select seq,val,

                       RowID as Row_ID,

                       Row_Number() over(order by seq desc) as RevSeq

                       from TestTable)) b

        where b.Row_ID = a.RowID)

where seq between 2 and 5;

13. Re: changing the order sequence

user520824 Oct 5, 2007 4:06 PM (in response to 481618)

le848dave,
Greatly appreciated all the excellent logic. there is something needs to be corrected here ... i use your logic with CASE
bu the data is not updated correctly, lemme copy it here..

see the last data set, its not getting changed from 5 to 2 ...

SQL> select * from test1;

GROUP_ID PRIORITY VALUE
---------- ---------- ----------
1 1 100
1 2 200
1 3 300
1 4 400
1 5 500
1 6 600

6 rows selected.

SQL> exec PKG_rbe_group_hist.prc_change_priority(1,2,5,:x);

PL/SQL procedure successfully completed.

SQL> select * from test1;

GROUP_ID PRIORITY VALUE
---------- ---------- ----------
1 1 100
1 5 200
1 2 300
1 3 400
1 4 500
1 6 600

6 rows selected.

SQL> exec PKG_rbe_group_hist.prc_change_priority(1,5,2,:x);

PL/SQL procedure successfully completed.

SQL> select * from test1;

GROUP_ID PRIORITY VALUE
---------- ---------- ----------
1 2 100
1 5 200
1 1 300
1 3 400
1 4 500
1 6 600
Like Show 0 Likes(0)

Actions

14. Re: changing the order sequence

Aketi Jyuuzou Oct 5, 2007 4:09 PM (in response to Aketi Jyuuzou)

Oh, I misunderstood the request of OP.

select group_id,seq,val,

case when seq = 5 

     then Lag(Val,5-2) over(order by seq)

     when seq between 2 and 5

     then Lead(Val,1) over(order by seq)

     else Val end as NewVal

  from TestTable

order by Val;

GROUP_ID  SEQ  VAL  NEWVAL

--------  ---  ---  ------

       1    1  100     100

       1    2  200     300

       1    3  300     400

       1    4  400     500

       1    5  500     200

       1    6  600     600

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.