Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86

    Question Unanswered: Cursor Issue - Order not correct

    I'm using a cursor in SQL Server 2000 to assist me in calculating for each store, the Sales Rank of a zip code. There are about 1500 stores, and 125,000 store/sales/zip code records.

    I am finding that this works for about 95% of the stores, but about 5% are getting fouled up, where the store's records are getting split in the sequencing, and so the store ends up with two zips ranked 1, two ranked 2, two ranked 3, etc.

    In the DB structure, there is a constraint restricting one record per store (org_id) per zip code (postalcode).

    Here's my code. Basically what I'm trying to have the cursor do is go through the table, ordered by org_id (store) asc, org_criteria_value (sales) desc, and rank the zip codes. When a new store is encountered, reset the counter to 1 and start ranking again. Do this until all the records are processed.

    /*CREATE Sales_Table table */
    CREATE TABLE [dbo].[Sales_Table] (
    [count_id] [int] NULL ,
    [org_id] [int] NULL ,
    [postalcode] [varchar] (20) NULL,
    [sales] [numeric](18,6) NULL ,
    [sales_rank] [integer] NULL,
    [org_criteria_input_date] [datetime] NULL
    ) ON [PRIMARY]


    insert into Sales_Table
    select 0 as count_id, omd.org_id, omd.postalcode, omd.org_criteria_value, 0 as cum_rank, org_criteria_input_date
    from org_model_data omd
    join org o on o.org_id = omd.org_id
    where o.client_id = @ClientID
    and model_Criteria_id = 27
    and org_criteria_value <> 0
    order by omd.org_id asc, omd.org_criteria_value desc


    -- DECLARE CURSOR for Sales_Table

    declare SalesRankCursor CURSOR
    SCROLL dynamic FOR
    select org_id, sales, sales_rank
    from Sales_Table
    for update of sales_rank


    -- CREATE LOOP TO UPDATE SALES RANK in Sales_Table with valid values

    OPEN SalesRankCursor

    while exists (Select * from Sales_Table where sales_rank = 0)
    Begin


    FETCH NEXT FROM SalesRankCursor

    set @StoreNext = @StoreCurrent
    set @SalesRank = (@SalesRank + 1)

    update Sales_Table
    set @StoreCurrent = org_id
    where current of SalesRankCursor

    if @StoreCurrent <> @StoreNext
    begin
    set @SalesRank = 1
    end

    update Sales_Table
    set sales_rank = @SalesRank
    where current of SalesRankCursor

    End


    CLOSE SalesRankCursor

    DEALLOCATE SalesRankCursor


    Any ideas?

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You insert the data ordered, but since the order of the rows are irrelevant to SQL Server, you are not guaranteed to get the rows out in the same order. So, what you basically need is to have the order by in the cursor definition.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    That's what I thought, but how do you order within the cursor definition. When I try an 'order by' statement within the cursor definition, I get a message saying it can only be used in an 'read only' cursor, and my cursor needs to allow an update.

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.

    I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.

    Thanks.

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by acg_ray
    What I've done for a solution is put a Clustered Index on my source table 'Test Table' on the fields org_id, and org_criteria_value. This appears to have resolved the issue.
    You should be aware that just because it has a clustered index on it, you are not GUARANTEED that the results will be returned in the expected order.

    Quote Originally Posted by acg_ray
    I would have preferred to have solved the problem within the cursor, however, so if anyone has a suggestion I would appreciate it.

    Thanks.
    Maybe I missed something in the code, but how about something like:

    Code:
    DECLARE SalesRank CURSOR
    READ_ONLY
    FOR SELECT Store_ID
    FROM Stores
    ORDER BY Sales DESC
    
    DECLARE @Store_ID int, @Counter int
    
    SELECT @Counter = 1
    FETCH NEXT FROM SalesRank INTO @Store_ID
    WHILE (@@fetch_status <> -1)
    BEGIN
    	IF (@@fetch_status <> -2)
    	BEGIN
    		UPDATE Stores
                              SET SalesRank = @Counter
                              WHERE Store_ID = @Store_ID
    	END
                 SELECT @Counter = @Counter + 1
    	FETCH NEXT FROM SalesRank INTO @Store_ID
    END
    
    CLOSE SalesRank
    DEALLOCATE SalesRank
    This would not handle ties or other things, but it should work (with some modification).

    Also, there is a RANK function in SQL 2005. That might be an option for you.

    Regards,

    hmscott
    Have you hugged your backup today?

  6. #6
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    HMScott,

    Let me take a shot at that. I may have to play around with it because I can't have a tie... I'll have to figure out a tie breaker and work from there.

    The Clustered IX appeared to solve my problem, but it wasn't the solution I wanted because of lack of certainty. Hopefully the approach you're recommending will work.

    Thanks.

Posting Permissions

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