Order a list of elements

Order a list of elements

Post by Luc » Thu, 28 Nov 2002 23:12:07



have a problem, with this data:

ID --  ORD
12     1
15     2
14     3
78     4

and so.... I want move up or downn on row on other (reorder the list
from client app), for ex

ID --  ORD
15     1  -->move up
12     2
14     3
78     4

and so... wich SQL instructions I use for this logic???

any suggestion????

 
 
 

Order a list of elements

Post by Jay Allar » Tue, 03 Dec 2002 14:53:25


-- what is the starting value? --

  from YourTable

-- what's the displayorder of the element above it?
  select

  from
   YourTable
  where

-- found. swap them.

  begin
        -- move the old one to the far end... 255 is far out of range (in my
case, anyway)
       update YourTable
       set DisplayOrder = 255

       -- change the display order of the row you want to move up
        update YourTable


        --now move the old row to it's new position
       update YourTable

       where DisplayOrder = 255
  end

This may leave gaps, but accomplishes the goal.   IE: If your display orders
are 3,5,7, and you move 7 up, then the display orders will remain 3,5,7, but
5 and 7 swap.


Quote:> have a problem, with this data:

> ID --  ORD
> 12     1
> 15     2
> 14     3
> 78     4

> and so.... I want move up or downn on row on other (reorder the list
> from client app), for ex

> ID --  ORD
> 15     1  -->move up
> 12     2
> 14     3
> 78     4

> and so... wich SQL instructions I use for this logic???

> any suggestion????