Forum Stats

• 3,734,034 Users
• 2,246,862 Discussions

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

changing the order sequence

Member Posts: 928 Bronze Badge
edited October 2007
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?

• Member Posts: 2,305
If it's just an onetime job, just update the values corresponding to that sequence.
• 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);
• 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....
• 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
• 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 =
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.```
• 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);```
• 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```
• 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.```
• 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.
• 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 )
• 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
)```
• 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;```
• 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
• 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.