|
|
Posted by Tim Martin on 12/16/47 11:57
version2 wrote:
> Does any know where a good change order for navigation script or
> tutorial is?
>
> Cause this is what i want to accomplish.
You sent me a private email on the subject, but if I'm going to help you
I'll do it in public so that other people can (potentially) benefit from it.
If you don't mind, I'll repeat what I understand of your problem to see
whether I've got it right. You have some a table with some elements that
you want to order in some way:
A 10
B 20
C 30
D 40
E 50
The second column being used to sort in the order you want. You're
leaving gaps between the numbers so that you can easily shuffle lines
around and get a different ordering, e.g.
A 10
C 30
B 35
D 40
E 50
Having done that you want to renumber the sort field so there are again
even gaps in it
A 10
C 20
B 30
D 40
E 50
The thing is, I think this is the wrong way to go about the task. If
what you want is an ordering, SQL can do that without you having to keep
track of the gaps between numbers yourself. Off the top of my head, you
could do something like:
A 1
B 2
C 3
D 4
E 5
And if you want to swap B and D, simply
-- Shunt all the contents after the target space up
UPDATE table
SET order_key=order_key + 1
WHERE order_key >= 4
-- Move target element to target space
UPDATE table
SET order_key=4
WHERE other_col='B'
-- Shunt back to fill the hole
UPDATE table
SET order_key=order_key - 1
WHERE order_key >= 3
There are probably better ways to do this, but maybe this puts you on
the right track.
Of course, with very large tables this solution may perform worse. But
the kind of data on which you would want to do this sort of manual
ordering is (I would imagine) not going to have large enough numbers to
make this significant. YMMV. Always test before you optimise.
Tim
Navigation:
[Reply to this message]
|