Forum Stats

  • 3,734,034 Users
  • 2,246,862 Discussions
  • 7,857,003 Comments

Discussions

changing the order sequence

user520824
user520824 Member Posts: 928 Bronze Badge
edited October 2007 in SQL & PL/SQL
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?

Comments

  • 229023
    229023 Member Posts: 2,305
    If it's just an onetime job, just update the values corresponding to that sequence.
  • user520824
    user520824 Member Posts: 928 Bronze Badge
    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);
  • user520824
    user520824 Member Posts: 928 Bronze Badge
    also it can change from any order to a difference sequence.

    i can change the sequence of 6 to 2 , 5 to 4 etc....
  • 74500
    74500 Member Posts: 161
    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
  • 481618
    481618 Member Posts: 211
    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.
  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    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);
  • 481618
    481618 Member Posts: 211
    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
  • 481618
    481618 Member Posts: 211
    edited October 2007
    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.
  • Sentinel
    Sentinel Member Posts: 1,284 Silver Badge
    edited October 2007
    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.
  • user520824
    user520824 Member Posts: 928 Bronze Badge
    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 )
  • 481618
    481618 Member Posts: 211
    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
    )
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited October 2007
    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;
  • user520824
    user520824 Member Posts: 928 Bronze Badge
    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
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited October 2007
    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
This discussion has been closed.