Forum Stats

  • 3,733,256 Users
  • 2,246,740 Discussions
  • 7,856,637 Comments

Discussions

help with select/update?

474368
474368 Member Posts: 248
edited June 2007 in SQL & PL/SQL
Basically I want to have table to behave like the "netflix" movie queue priority where a user can update a row ( or any number of rows) in the queue to be any priority and the queue sorts out the new order of the queue.

Can this be done with a select?

Say I have a table with 3 columns Such as:
prio	new_prio	name

1	1		First record
2	2		Second record
3	3		Third Record
4	4		Forth Record
5	5		Fifth Record
6	6		Sixth Record
What I want to happen is that the prio, to always be sequence, starting at 1, incrementing for each record for the whole table.

So if I took the same table and updated the new_prio as follows:
prio	new_prio	name

1	1		First record
2	2		Second record
3	2		Third Record  <- updated this record  new_prio = 2
4	4		Forth Record
5	3		Fifth Record  <- updated this record  new_prio = 3
6	6		Sixth Record
I would want to update the prio column to follow the updates from the new_prio column So the results would look like (sorted on prio):
prio	new_prio	name

1	1		First record
2	2		Third Record
3	3		Fifth Record
4	4		Second record
5	5		Forth Record
6	6		Sixth Record
Now say I delete the two records like:
prio	new_prio	name

1	1		First record
2	2		Third Record  <- delete this one 
3	3		Fifth Record  <- and this one
4	4		Second record
5	5		Forth Record
6	6		Sixth Record
I would expect the following:
prio	new_prio	name

1	1		First record
2	2		Second record
3	3		Forth Record
4	4		Sixth Record
Is there a way to get this done in a simple select or update? what about a simple cursor loop?

I know I could do this with a lot of pl/sql code, But I am very interested if this could be done in sql. I have come close with using a cursor loop with inline views using rownum, but I am hitting problems with moving more then 2 rows around. when updating multiple rows, some rows come out one or two rows out of their intended order/prio.

Thanks in advance.

--pat

Comments

  • 519688
    519688 Member Posts: 2,646
    create table netflix ( o number, n number, t varchar2(10) );
    
    insert into netflix 
    select rownum, rownum, to_Char(trunc(sysdate,'mon')-1+rownum,'DDsp') 
    from dual connect by level <= 11;
    
    select * from netflix;
    
             O          N T
    ---------- ---------- ----------
             1          1 ONE
             2          2 TWO
             3          3 THREE
             4          4 FOUR
             5          5 FIVE
             6          6 SIX
             7          7 SEVEN
             8          8 EIGHT
             9          9 NINE
            10         10 TEN
            11         11 ELEVEN
    
    update netflix set n = 2 where o=3 ;
    update netflix set n = 3 where o=5 ;
    
    select * from netflix order by o;
    
             O          N T
    ---------- ---------- ----------
             1          1 ONE
             2          2 TWO
             3          2 THREE
             4          4 FOUR
             5          3 FIVE
             6          6 SIX
             7          7 SEVEN
             8          8 EIGHT
             9          9 NINE
            10         10 TEN
            11         11 ELEVEN
    
    select o, n, t from netflix order by n, sign(abs(o-n)) desc, o;
    
             O          N T
    ---------- ---------- ----------
             1          1 ONE
             3          2 THREE
             2          2 TWO
             5          3 FIVE
             4          4 FOUR
             6          6 SIX
             7          7 SEVEN
             8          8 EIGHT
             9          9 NINE
            10         10 TEN
            11         11 ELEVEN
    
    update netflix set (o,n) = (
     select rn, rn from (
       select o, rownum rn from (
          select o, n, t from netflix order by n, sign(abs(o-n)) desc, o
        )
     )
     where o = netflix.o
    )
    /
    
    select * from netflix order by o;
    
             O          N T
    ---------- ---------- ----------
             1          1 ONE
             2          2 THREE
             3          3 TWO
             4          4 FIVE
             5          5 FOUR
             6          6 SIX
             7          7 SEVEN
             8          8 EIGHT
             9          9 NINE
            10         10 TEN
            11         11 ELEVEN
    
    11 rows selected.
  • 474368
    474368 Member Posts: 248
    Thanks for your suggestions shoblock, your solution is very similar to what I was working towards. More elegant with the use of the formulas in the sort. I was using a case statement in the sort to achieve similar results.

    I am having continued issues though. Suppose I had the following setup, notice the multiple reassignments
    select rowid,n.* from netflix n  order by o
    
    O  N    T  
    ---------------
    1  1    FIVE       
    2  2    SIX        
    3  3    ONE        
    4  9    FOUR     <- moved to prio 9   
    5  9    EIGHT    <- moved to prio 9     
    6  9    NINE     <- moved to prio 9     
    7  7    SEVEN      
    8  8    TWO        
    9  3    THREE    <- moved to prio 3 
    10 3    TEN      <- moved to prio 3 
    11 11   ELEVEN     
    The results are as follows. note that row is not correct as it is one less in the prio then what it should be. I included the rownum to help identify.
    SELECT o, n, t, ROWNUM
      FROM (SELECT   o, n, t
                FROM netflix
            ORDER BY n, SIGN(ABS(o - n)) DESC, o) s
    
    O  N  T          ROWNUM                                 
    
    1  1  FIVE       1                                      
    2  2  SIX        2                                      
    9  3  THREE      3                                      
    10 3  TEN        4                                      
    3  3  ONE        5                                      
    7  7  SEVEN      6                                      
    8  8  TWO        7                                      
    4  9  FOUR       8    <- this one was supose to be prio 9                                     
    5  9  EIGHT      9                                      
    6  9  NINE       10                                     
    11 11 ELEVEN     11                                     
    Any suggestions? Maybe I should not allow the user to enter duplicate reorders..... That is another can of worms though....
  • 519688
    519688 Member Posts: 2,646
    > note that row is not correct as it is one less in the prio then what it should be

    and that's why netflix doesn't allow you to reorder multiple rows to the same new row - how's it supposed to figure out what you meant.
    "ALERT: When you try to give several movies the same priority in your Queue, only the last movie you put in that position will be given that priority ranking. Please make sure that each movie you reposition is given a different number."

    you moved 3 things to number 9, and two things to number 3. first off, that would be poor editing on the end users part. 2nd, it worked. it moved the 3 things that you changed to "9" to the spot(s) after "8". really can't complain about that.
  • 474368
    474368 Member Posts: 248
    edited June 2007
    OK. Sorry, not complaning, I was not clear. Yes I was thinking the same thing as netfix error. I really appreciate your help here. Although, my previous example had multiple duplicates as part of the new prio, you can get the same erroneous results when using perfectly fine values to reassign the prio. Take a look at the following example. In this example I do not have any duplicate re-assigns.
      select rowid,n.* from netflix n  order by o
    
    O   N   T          
    -------------------------
    1   1   ONE        
    2   2   TWO        
    3   9   THREE      
    4   10  FOUR       
    5   5   FIVE       
    6   6   SIX        
    7   7   SEVEN      
    8   1   EIGHT      
    9   2   NINE       
    10  3   TEN        
    11  11  ELEVEN   
    The results show:
      SELECT o, n, t, ROWNUM
      FROM (SELECT   o, n, t
                FROM netflix
            ORDER BY n, SIGN(ABS(o - n)) DESC, o) s
    
    O  N   T          ROWNUM                                 
    ---------------------------------------
    8  1   EIGHT      1                                      
    1  1   ONE        2       <- this one should be 4                               
    9  2   NINE       3       <- this one should be 2                                    
    2  2   TWO        4                                    
    10 3   TEN        5       <- this one should be 3                               
    5  5   FIVE       6                                      
    6  6   SIX        7                                      
    7  7   SEVEN      8                                      
    3  9   THREE      9                                      
    4  10  FOUR       10                                     
    11 11  ELEVEN     11                                     
  • 519688
    519688 Member Posts: 2,646
    > Sorry, not complaning,
    no need to apologize. the prior example was fine. now this one is something to complain about.
  • 474368
    474368 Member Posts: 248
    OK, I understand. I think this is an interesting puzzle. Hope someone jumps in with a good solution. Note my edit to my last example, there are actually 3 prio in the wrong place :(

    > Sorry, not complaning,
    no need to apologize. the prior example was fine.
    now this one is something to complain about.
  • John Spencer
    John Spencer Member Posts: 8,567
    Not being a netflix user, maybe I'm missing something basic here but your expectation of the order does not make sense to me (more so after you changed it under when I got distracted while posting).
    8  1   EIGHT      1
    1 1 ONE 2 <- this one should be 4
    9 2 NINE 3 <- this one should be 2
    2 2 TWO 4
    10 3 TEN 5 <- this one should be 3
    The first sort column is n, and the last time I did math in school, 1 is less than 2 so how should 1, 1, ONE sort after 9, 2, NINE?

    John
  • 474368
    474368 Member Posts: 248
    John,

    In this example 'o' is the original priority order, 'n' is the new priority which is what gets updated by a user who wants to change the order. So in this case the user wanted record 'NINE' to be the second row in the priority. record 'TEN' to be the 3rd etc..

    Since the new priority is 2, number nine should be row 2, pushing the previous row down the q.

    Does that clear it up?

    basically the netflix q, shows the list of movies in order which they were placed in the queue, the user has the option to assign a new priority to any of those movies. This priority overrides the default.

    --pat
    Not being a netflix user, maybe I'm missing something
    basic here but your expectation of the order does not
    make sense to me (more so after you changed it under
    when I got distracted while posting).
    8  1   EIGHT      1
    1 1 ONE 2 <- this one should be 4
    9 2 NINE 3 <- this one should be 2
    2 2 TWO 4
    10 3 TEN 5 <- this one should be
    3
    The first sort column is n, and the last time I did
    math in school, 1 is less than 2 so how should 1,
    1, ONE sort after 9, 2, NINE?

    John
  • ajallen
    ajallen Member Posts: 1,796
    edited June 2007
    Retaining a 1, 2, 3 sequence without holes is an unreasonable and unnecessary requirement. All you need is that the next lowest priority name have a value greater than the current name and less than those that follow in priority. No holes in the priority sequence is not needed.
    Starting with...
    prio name

    1 First record
    2 Second record
    3 Third Record <- updated this record new_prio = 2
    4 Forth Record
    5 Fifth Record <- updated this record new_prio = 3
    6 Sixth Record

    1) update tab set prio = prio + 1 where prio > = new_prio
    giving

    prio name
    1 First record
    3 Second record
    4 Third Record <- updated this record new_prio = 2
    5 Forth Record
    6 Fifth Record <- updated this record new_prio = 3
    7 Sixth Record

    2) update tab set prio = new_prio where name = 'Third Record'
    giving

    prio name
    1 First record
    3 Second record
    2 Third Record <- updated this record new_prio = 2
    5 Forth Record
    6 Fifth Record <- updated this record new_prio = 3
    7 Sixth Record

    3) update tab set prio = prio + 1 where prio > = new_prio
    giving

    prio name
    1 First record
    4 Second record
    2 Third Record <- updated this record new_prio = 2
    6 Forth Record
    7 Fifth Record <- updated this record new_prio = 3
    8 Sixth Record

    4) update tab set prio = new_prio where name = 'Fifth Record'
    giving

    prio name
    1 First record
    4 Second record
    2 Third Record <- updated this record new_prio = 2
    6 Forth Record
    3 Fifth Record <- updated this record new_prio = 3
    8 Sixth Record

    5) select name, rownum as priority
    from (select name from tab order by prio);
    giving

    priority name
    1 First record
    2 Third Record <- updated this record new_prio = 2
    3 Fifth Record <- updated this record new_prio = 3
    4 Second record
    5 Forth Record
    6 Sixth Record
    -- so, each update is two simple steps, if you do not care about having holes in you priority sequence stored in the table.
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    SQL> create table netflix
    2 as
    3 select 1 o, 1 n, 'ONE' t from dual union all
    4 select 2, 2, 'TWO' from dual union all
    5 select 3, 9, 'THREE' from dual union all
    6 select 4, 10, 'FOUR' from dual union all
    7 select 5, 5, 'FIVE' from dual union all
    8 select 6, 6, 'SIX' from dual union all
    9 select 7, 7, 'SEVEN' from dual union all
    10 select 8, 1, 'EIGHT' from dual union all
    11 select 9, 2, 'NINE' from dual union all
    12 select 10, 3, 'TEN' from dual union all
    13 select 11, 11, 'ELEVEN' from dual
    14 /

    Tabel is aangemaakt.

    SQL> SELECT o, n, t
    2 from netflix
    3 /

    O N T
    ---------- ---------- ------
    1 1 ONE
    2 2 TWO
    3 9 THREE
    4 10 FOUR
    5 5 FIVE
    6 6 SIX
    7 7 SEVEN
    8 1 EIGHT
    9 2 NINE
    10 3 TEN
    11 11 ELEVEN

    11 rijen zijn geselecteerd.

    SQL> select i, t
    2 from dual
    3 model
    4 reference changed_numbers on
    5 ( select n, t
    6 from netflix
    7 where o <> n
    8 )
    9 dimension by (n)
    10 measures (t)
    11 reference unchanged_numbers on
    12 ( select row_number() over (order by n) n
    13 , t
    14 from netflix
    15 where o = n
    16 )
    17 dimension by (n)
    18 measures (t)
    19 main m
    20 dimension by (1 i)
    21 measures (cast(null as varchar2(10)) t, 1 last_n)
    22 rules iterate (1000)
    23 until ( unchanged_numbers.t[last_n[1]] is null
    24 and changed_numbers.t[iteration_number+1] is null
    25 )
    26 ( t[iteration_number+1]
    27 = nvl
    28 ( changed_numbers.t[iteration_number+1]
    29 , unchanged_numbers.t[last_n[1]]
    30 )
    31 , last_n[1] = last_n[1] + nvl2(changed_numbers.t[iteration_number+1],0,1)
    32 )
    33 /

    I T
    ---------- ----------
    1 EIGHT
    2 NINE
    3 TEN
    4 ONE
    5 TWO
    6 FIVE
    7 SIX
    8 SEVEN
    9 THREE
    10 FOUR
    11 ELEVEN

    11 rijen zijn geselecteerd.
    Regards,
    Rob.
  • 519688
    519688 Member Posts: 2,646
  • John Spencer
    John Spencer Member Posts: 8,567
    Now, I think I understand. This seems to work, at least for your sample dataset.
    SQL> WITH netflix as (select 1 o, 1 n, 'ONE' t from dual union all
    2 select 2, 2, 'TWO' from dual union all
    3 select 3, 9, 'THREE' from dual union all
    4 select 4, 10, 'FOUR' from dual union all
    5 select 5, 5, 'FIVE' from dual union all
    6 select 6, 6, 'SIX' from dual union all
    7 select 7, 7, 'SEVEN' from dual union all
    8 select 8, 1, 'EIGHT' from dual union all
    9 select 9, 2, 'NINE' from dual union all
    10 select 10, 3, 'TEN' from dual union all
    11 select 11, 11, 'ELEVEN' from dual)
    12 SELECT o, n, t, rownum
    13 FROM (SELECT *
    14 FROM netflix
    15 ORDER BY CASE WHEN n<o THEN 1
    16 WHEN n=o THEN 2
    17 WHEN n>o THEN 3 END, n, o);

    O N T ROWNUM
    ---------- ---------- ------ ----------
    8 1 EIGHT 1
    9 2 NINE 2
    10 3 TEN 3
    1 1 ONE 4
    2 2 TWO 5
    5 5 FIVE 6
    6 6 SIX 7
    7 7 SEVEN 8
    11 11 ELEVEN 9
    3 9 THREE 10
    4 10 FOUR 11

    11 rows selected.
    Although, I knd of like Rob's model version.

    John
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Nice try, but it's not that simple ;-)

    Your row ELEVEN should stay on the last place.

    Regards,
    Rob.
  • 474368
    474368 Member Posts: 248
    Rob and all. Thanks for the input. I am at the ODTUG conference this week, so I will review this and give it a shot next week.

    Thanks again.

    --pat
  • John Spencer
    John Spencer Member Posts: 8,567
    Well, if you want to be picky :-)

    I can't help feeling that there is a performant way to do this without a model clause, but I'm not smart enough to see it.

    John
This discussion has been closed.