Re: [GENERAL] Auto Ordering

then, why use int, isn't char better? (almost no need for batch).



>From: Herouth Maoz <herouth@oumail.openu.ac.il>
>To: "Shawn T. Walker" <shawn@netcrafters.com>
>CC: pgsql-general@postgreSQL.org
>Subject: Re: [GENERAL] Auto Ordering
>Date: Mon, 1 Nov 1999 17:57:13 +0200
>
>At 20:41 +0200 on 27/10/1999, Stuart Rison wrote:
>
>
> > In the example you give, you could do the changes with two UPDATE
> > commands:
> >
> > 1) UPDATE questions SET order=0 WHERE order=5;
> > 2) UPDATE questions SET order=order+1 WHERE order<5;
> >
> > It becomes more tricky when you try and move a question to a position
> > other than the first one (e.g. question #6 to move to position #3 and
>all
> > other questions to be shifted accordingly).
> >
> > This would take three UPDATEs:
> >
> > 1) UPDATE questions SET order=0 WHERE order=6;
> > 2) UPDATE questions SET order=order+1 WHERE order>=3 and order<6;
> > 3) UPDATE questions SET order=3 WHERE order=0;
>
>Here is an alternative method of thinking which I used in the past - it
>depends on other factors whether this is good or not. If only the order of
>the questions is important, and not the actual number, then you can use
>fractions. You can use a floating point field, or a fixed point (numeric)
>one, or just an int field that normally gets the numbers 100, 200, 300.
>
>Changing order then becomes very easy:
>
>   UPDATE questions SET the_order=50 WHERE the_order=600;
>
>Will change questions 100,200,300,400,500,600,700
>To 50,100,200,300,400,500,700.
>
From time to time, though, you will have to renumber your questions, to
>make sure you don't run out of fraction precision. You can do that with
>something like:
>
>SELECT the_order
>INTO TABLE temp_numbers
>FROM questions
>ORDER BY the_order;
>
>CREATE SEQUENCE new_seq INCREMENT 100 START 100;
>
>UPDATE questions
>SET the_order = nextval( 'new_seq' )
>WHERE questions.the_order = temp_numbers.the_order;
>
>DROP SEQUENCE new_seq;
>DROP TABLE temp_numbers;
>
>The idea is to do the renumbering in batch, and have a small penalty in
>"real time".
>
>Herouth
>
>--
>Herouth Maoz, Internet developer.
>Open University of Israel - Telem project
>http://telem.openu.ac.il/~herutma
>
>
>
>************
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com