www.webdeveloper.com
Results 1 to 5 of 5

Thread: Help!! Sequence Numbers

  1. #1
    Join Date
    May 2007
    Location
    St. Louis, MO
    Posts
    36

    Help!! Sequence Numbers

    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

  2. #2
    Join Date
    Jul 2004
    Posts
    300
    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

  3. #3
    Join Date
    May 2007
    Location
    St. Louis, MO
    Posts
    36
    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
    Last edited by macker; 08-15-2007 at 02:48 PM.

  4. #4
    Join Date
    Jul 2004
    Posts
    300
    Did you upgrade? Can you just change the compatibility level, or are you just not running sql server 2005?

    Change this:
    Code:
    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:
    Code:
    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:
    Code:
    where
    	#t.sequencenumber = t.sequencenumber
    	and #t.val = t.val
    otherwise you'll have problems. let me know if you need any clarification.
    Last edited by mattyblah; 08-15-2007 at 04:21 PM. Reason: Ooops. you should use your table, #a, instead of #t.

  5. #5
    Join Date
    May 2007
    Location
    St. Louis, MO
    Posts
    36
    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
    Last edited by macker; 08-17-2007 at 09:41 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles