Help!! Sequence Numbers

[macker]

Using T-SQL, and MS SQL Management Studio 2005

I have a table that holds interest rate records.
Each record has a sequence number column that is used for viewing. When the select statement is called, it orders by this column.

I have enabled the users to change the sort order via an editable datagrid.
This update statement works, and it reorders them, however, if you change a record that has a sequence number of 3, and you change that to 14, it will order properly, however, now there is no 3 in the order.

I need the proc to reorder all the numbers so that no number is missing.

Example:

Before Update:
sequenceNumber
1
2
3
4
5
6
7
8
9

After an update on the sequenceNumber column:
change the 3, to 10 on the update and you now get

sequenceNumber
1
2
4
5
6
7
8
9
10

That is the problem, I need it to change the 3, to a 10, but then reorder all the other ones so in the end, it is still 1-9, but the one that had 3 as the sequence number, now has 9 as the sequence number, and all the other records are reordered properly.

I hope this is enough explaination. Any help would be great. The user could also change a 9 to a 3, in which case, I would like it to reorder the whole thing, as stated above.

(Note: There are 3 different catagories of records in this table, that are grouped by the catID. Each catagory has its own sequence number set. That is what the variable "@catID" means in the proc below.)

Thank you!

Stored Procedure Thus Far:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[details_update]
@depositType varchar(50),
@minToOpen varchar(50),
@maturity varchar(20),
@minBalanceToEarn varchar(50),
@interestRate decimal(18,3),
@apy decimal(18, 2),
@compFreq varchar(30),
@rateID int,
@sortNumber int
AS

declare @tempInterestRate numeric(18,3)
declare @tempApy numeric(18,2)
declare @catID int

select @tempInterestRate = interestRate fromdbo.rate_Details where rateID = @rateID
select @tempApy = apy from dbo.rate_Details where rateID = @rateID

select @catID = catID from dbo.rate_Details where rateID = @rateID

select rateID, sequenceNo
into #a
from dbo.rate_Details
where catID = 3--@catID

--select * from #a


if not(@interestRate = @tempInterestRate)
begin
update dbo.rate_Details
set
editDate = CONVERT(char(10), GetDate(), 101)
end

if not(@tempApy = @apy)
begin
update dbo.rate_Details
set
editDate = CONVERT(char(10), GetDate(), 101)
end

update dbo.rate_Details
set
depositType = @depositType,
minToOpen = @minToOpen,
maturity = @maturity,
minBalanceToEarn = @minBalanceToEarn,
interestRate = @interestRate,
APY = @apy,
compFreq = @compFreq,
sequenceNo = @sortNumber

where rateID = @rateID

[mattyblah]

here's a little example I put together. hope it can help you with your problem.


create table #t
(
sequencenumber int
, val int
)

insert into #t (sequencenumber, val) values (1, 1)
insert into #t (sequencenumber, val) values (2, 2)
insert into #t (sequencenumber, val) values (3, 3)
insert into #t (sequencenumber, val) values (4, 4)
insert into #t (sequencenumber, val) values (5, 5)
insert into #t (sequencenumber, val) values (6, 6)
insert into #t (sequencenumber, val) values (7, 7)
insert into #t (sequencenumber, val) values (8, 8)
insert into #t (sequencenumber, val) values (9, 9)

select * from #t
order by sequencenumber

update #t set sequencenumber = 10 where sequencenumber = 3

select * from #t
order by sequencenumber

update #t set
sequencenumber = t.rowid
from
(select *, row_number() over (order by sequencenumber) as rowid from #t) t
where #t.sequencenumber = t.sequencenumber

select * from #t
order by sequencenumber

drop table #t

[macker]

I can kind of see where you are going with this and I am implementing it.

However, when I run the last statement, I get this error.

'row_number' is not a recognized function name.
Any Ideas??


remember, I am using T-SQL with MS SQL MS 2005


EDIT: I don't know why it is doing this, that method is used in T-SQL
http://msdn2.microsoft.com/en-us/library/ms186734.aspx

EDIT II: I ran the select @@version, and it turns out, the server i work with is in-fact, running 2000. Can this still be done with 2000?

additions to proc:
select rateID, sequenceNo
into #a
from dbo.rate_Details
where catID = 3--@catID
order by sequenceNo

select * from #a
update #a
set sequenceNo = 10 where sequenceNo = 3

select * from #a
order by sequenceNo

update #a
set
sequenceNo = a.rowid
from (select *, row_number() over (order by sequenceNo) as rowid from #a) a
where #a.sequenceNo = a.sequenceNo


select * from #a
order by sequenceNo

[mattyblah]

Did you upgrade? Can you just change the compatibility level, or are you just not running sql server 2005?

Change this:

update #a
set
sequenceNo = a.rowid
from (select *, row_number() over (order by sequenceNo) as rowid from #a) a
where #a.sequenceNo = a.sequenceNo

To this:

update #t set
sequencenumber = t.rowid
from
	(
	select *, 
	(
	select count(*) from #t t2 where
		t1.sequencenumber > t2.sequencenumber or (t1.sequencenumber = t2.sequencenumber and t1.val > t2.val)
	) + 1 as rowid from #t t1
	) t
where
	#t.sequencenumber = t.sequencenumber
	and #t.val = t.val

Just as a side note, val must be a unique, non null value for this to work. Also, on the first query, it should have the same where clause of the second, that being:

where
	#t.sequencenumber = t.sequencenumber
	and #t.val = t.val

otherwise you'll have problems. let me know if you need any clarification.

[macker]

I implemented the code you suggested, and it does seem to work.

There is a small problem with it, though.
It is "off-by-one" when it is updating

If you change sequence numbers:

LITTLE to BIG = 1 less than it should be
3 to 10 = 9 (not 10)

BIG to LITTLE = 1 more than it should be
10 to 3 = 4 (not 3)


EDIT: But it does re-order all the numbers! It just does this for the one you are trying to change.

I hope this is a good explaination. I have included the select sql i have implemented below:
Thanks again for your help


declare @catID int
declare @tempSequenceNo int
declare @oldNumber int
declare @rateID int
declare @sortNumber int

set @catID = 3
set @sortNumber = 3 (this is for testing, this select goes withing a stored proc)
set @rateID = 23

select * into #a
from dbo.rate_Details
where catID = @catID
order by sequenceNo

--set the values for variables----------------------------
select @tempSequenceNo = sequenceNo from #a --this is to get all of the current sequence numbers
select @oldNumber = sequenceNo from #a where rateID = @rateID --this is to get the original sequence number from the one that is being changed.

--update the temp table to hold the new sequence number for the record passed to be updated
update #a
set sequenceNo = @sortNumber
where rateID = @rateID


select * from #a
--update #a
--set
--sequenceNo = a.rowid
--from (select *, row_number() over (order by sequenceNo) as rowid from #a) a
--where #a.sequenceNo = a.sequenceNo


update #a set
sequenceNo = a.rowid
from
(select *,
(select count(*)
from #a a2
where
a1.sequenceNo > a2.sequenceNo or (a1.sequenceNo = a2.sequenceNo and a1.uniqueColumn > a2.uniqueColumn)
) + 1 as rowid from #a a1
) a
where
#a.sequenceNo = a.sequenceNo
and #a.uniqueColumn = a.uniqueColumn


update #a
set
uniqueColumn = sequenceNo
where catID = @catID

select * from #a
order by sequenceNo

drop table #a