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?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.|||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.|||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.|||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.

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:

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|||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.