http://qs321.pair.com?node_id=438483


in reply to reordering a stack with little effort

Instead of this system of using fractional question numbers, I would consider giving the questions a simple incrementing integer for identification, and having the order of the questions be stored by other means. A simple system for maintaining order might be a linked list. A table to hold this information could look something like this:

 id | next
----+------
  1 |    2
  2 |    3
  3 |    4
  4 | NULL

Then, if someone asks you to insert a question between 2 and 3, you would simply have to change it like so:

 id | next
----+------
  1 |    2
  2 |    5
  5 |    3
  3 |    4
  4 | NULL

Replies are listed 'Best First'.
Re^2: reordering a stack with little effort
by sgifford (Prior) on Mar 11, 2005 at 06:57 UTC
    Cool idea, but I can't think of how you would ask SQL to put the list in order for you...

      In Oracle you can use CONNECT BY to do this:

      SELECT id FROM thetable START WITH id = 1 CONNECT BY PRIOR next = id
      this is actually a very nice idea... you would simply ORDER BY NEXT instead of ORDER BY QUESTION_NUM. To tell the truth, I thought of using this method, but chose to implement the decimal system instead. I still think this is better, but since my decimal system is already working, I am gonna leave it in place. For my next implementation, I am going to use a linked list.
      --

      when small people start casting long shadows, it is time to go to bed
        you would simply ORDER BY NEXT instead of ORDER BY QUESTION_NUM

        Sadly, no -- once you start rearranging the questions, the values in the "next" column need not be in ascending numerical order... I think you'll need to sort them in Perl rather than at the SQL DBMS.