Increment/Decrement Help 2

[BG12424]

I have a table with a column designated for ordering page content. I was wondering what SQL I could use to auto-increment/auto-decrement order numbers in a column. For example if my columns looked like this:

PageId PageOrder
------ ----------
1      1 
2      2
3      3
4      4
5      5
6      6
7      7

...and I want to change the page order for pageId 3 to be 5, then all the page orders greater than pageorder of 3 will be decremented until the updated pageorder of 5. This will now look like this:

PageId PageOrder
------ ----------
1      1 
2      2
3      5
4      3
5      4
6      6
7      7

Can someone please help with this? Thank you

regards,
Brian

http://www.gainesconsulting.com

The problem with doing something right the first time is that nobody appreciates how difficult it was.

 

[ESquared]

Well, I see the flaw, and it's the same flaw I had with my original idea. Too bad I missed it.

So, to correct it, you have to know not just the PageOrder of the ID you're moving something after, but also the PageOrder of the one you're moving before. Then just pick a value somewhere between them.

The reason I chose text is because then you have a definite time to trigger a full reorder: when the number of digits reaches the maximum. Let's say you choose 10 characters. then when you add the tenth character, you reorder the whole thing. Since this will be done in a stored procedure a quick check for this wouldn't be too much of a problem, would it? It could be made to work for decimal, too.

 

[r937]

and my point earlier was that if you have to "reorder the whole thing" someday, what could be simpler than to reset an identity column

if i'm going to do something infrequently, i sure don't want to spend time writing tight but complex stored proc logic for it



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

But identity columns only definitively give record insert order (and records inserted in the same operation have no guarantee of ordering). Resetting an identity column for already existing records should be treated as generating random numbers. At least, I think this is the case!

 

[r937]

okay, let's demonstrate

create table t1
( id integer not null primary key identity 
, sortkey integer not null 
, foo varchar(9)
)
insert into t1 (sortkey,foo) 
 values (100,'curly')
insert into t1 (sortkey,foo) 
 values (200,'shemp')
insert into t1 (sortkey,foo) 
 values (300,'joe')
insert into t1 (sortkey,foo) 
 values (400,'moe')
insert into t1 (sortkey,foo) 
 values (500,'curly joe')
insert into t1 (sortkey,foo) 
 values (600,'larry')

now let's resequence them a bit

update t1 set sortkey = 150 where sortkey = 600
update t1 set sortkey = 440 where sortkey = 200
update t1 set sortkey = 460 where sortkey = 300

select * from t1 order by sortkey

here's the sorted results:

1	100	curly
6	150	larry
4	400	moe
2	440	shemp
3	460	joe
5	500	curly joe

okay, now let's say it's time to resequence them

create table t2
( id integer not null primary 
, sortkey integer not null 
     primary key identity (100,100)
, foo varchar(9)
)

insert into t2 (id,foo)
select id,foo from t1 order by sortkey

truncate table t1

insert into t1 (sortkey,foo)
select sortkey,foo from t2 order by sortkey

select * from t1 order by sortkey

and voila --

1	100	curly
2	200	larry
3	300	moe
4	400	shemp
5	500	joe
6	600	curly joe

correctly resequenced and still in the right order

yes, you have to "take the table down for repairs" for a moment (a minute? two?), but remember, this is supposed to happen only infrequently

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

Like I said... inserting, not updating. But are you 100% sure that the order by will be enforced even for very large datasets?

 

[r937]

me? 100% sure of SQL Server honouring an ORDER BY clause in an INSERT SELECT statement?

ha!!

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

That's not much of an answer. Got a BOL reference? I've learned not to assume things such as ordering of rows when it's not perfectly clear. Call it one of the ways I try to keep in the "brilliant" category... knowing when I don't know and finding the answer from authoritative sources instead of assuming I know.

 

[donutman]

Ok, got it. Updating Rows with Row Count
thread183-980264 has the code I was looking for.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

don't get mad at me, you're my idol, remember?

you're saying my method isn't 100% guaranteed?

insert into t2 (id,foo)
select id,foo from t1 order by sortkey

you're saying that this ORDER BY here is not going to insert rows into t2 in the right order?

ah, SQL Server, ya gotta love it

however, i would be inclined to use it anyway

hey, i bet i can easily discover if the renumbering did not work correctly

yup, just run another query

well, my work here is done

you coders let me know when your solution has made it to production

i've got the weekend off





rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

It is fitting that you worship me, but try to be a tad more respectful of your god, eh?

No, I wasn't mad, just looking for proof is all.

 

[donutman]

Rudy, vongrunt has already proved that the identity insert thingee doesn't work with large datasets. The problem is that SQL Server breaks up the sort into batches and gets an identity value prior to the final merge of the various batches.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Here's the link.

http://www.winnetmag.com/SQLServer/Article/ArticleID/43553/43553.html

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[r937]

thanks for the link, karl

that article makes sense



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

Trust donutman to come through with the goodies!