help with select/update?

474368

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

519688

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

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

> 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

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

> Sorry, not complaning,
no need to apologize. the prior example was fine. now this one is something to complain about.

474368

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

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

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

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

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

Wow!

John Spencer

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

Nice try, but it's not that simple ;-)

Your row ELEVEN should stay on the last place.

Regards,
Rob.

474368

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

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