0

I got a very special problem that I'd like to solve in SQL. I need to make sure that optionOrder for the same questionID goes from 0-[any number].

So for example the rows with questionID = 18386, their optionOrder are right now 1,2,3,4. They need to be 0,1,2,3.

Also if the rows are like this 1,2,4, it needs to be 0,1,2

I'm sorry for the incorrect grammars.

enter image description here

  • So, what do you want to do? renumber the options after ordering them ? – A Hocevar Aug 19 '15 at 13:55
  • maybe leave them the way they are, and harness the power of the `order by` clause – Drew Aug 19 '15 at 13:56
  • You might want to have a look at http://stackoverflow.com/questions/1895110/row-number-in-mysql – A Hocevar Aug 19 '15 at 13:57
  • @AHocevar I want to update the table so that **optionOrder** ALWAYS goes from 0-[1|2|3|4] and so on. –  Aug 19 '15 at 13:58
  • the optionOrder is always `0-[1|2|3|4] and so on` if you use the right `order by` clause, without screwing up other dependent child tables that rely on `id` that you just messed up by doing an update – Drew Aug 19 '15 at 14:02

1 Answers1

2

In MySQL, you can do this with variables:

set @rn := 0;
set @q := -1;

update table t
    set optionorder = (case when @q = questionid then (@rn := @rn + 1)
                            when (@q := questionid) is not null then (@rn := 0)
                            else -1 -- should never happen
                       end)
    order by questionid, optionorder;

Because of the order by, you need to set the variables outside the update.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Works perfect! I've fiddled with variables in my last attempt but I never managed to the get if statement working, thank you a lot! –  Aug 19 '15 at 14:48