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

    changing the order sequence

    user520824
      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
          If it's just an onetime job, just update the values corresponding to that sequence.
          • 2. Re: changing the order sequence
            user520824
            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);
            • 3. Re: changing the order sequence
              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....
              • 4. Re: changing the order sequence
                74500
                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
                • 5. Re: changing the order sequence
                  481618
                  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
                    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
                      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
                        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
                          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.
                          • 10. Re: changing the order sequence
                            user520824
                            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 )
                            • 11. Re: changing the order sequence
                              481618
                              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
                                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
                                  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
                                  • 14. Re: changing the order sequence
                                    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