Not a problem - it would only be a problem when you stop learning - and even then, it's YOUR problem! :-D:-P;-):w00t:

In the original post, the OP shows the expected results in ckid order, and the grp number changes whenever the stat changes. That is why there is a clustered index on the ckid column. The ROW_NUMBER() ... ORDER BY clause MUST identical to the clustered index columns (including the sort direction).

The ROW_NUMBER() ORDER BY is a neat trick that Paul White came up with, and it was enhanced by Tom Thompson. It essentially guarantees that the update occurs in the proper order, or not at all. Tom's enhancement covers the (theoretical) possibility that the sequence number is properly generated in a linear fashion, while the variable assignment (that's being carried row-to-row) isn't.

In the solution I posted, I alluded to the possibility of needing an additional field to do this properly. I'm assuming that there would be an account number. This would require:

1. changing the clustered index (and the ROW_NUMBER()) to include this column.

2. changing the logic of the variable assignment to handle when the account number changes.

I've found that the best way of learning is by practicing. You don't get rid of c.u.r.s.o.r.s. by writing more of them; you learn the QU (and other set-based practices) by doing things with them. So, let's make a couple of minor modifications to the specification. Let's include the account number, and change the specs so that whenever the account number changes, the grp restarts at one. All other specs remain the same. The following code has the sample data and expected results.

IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp;

CREATE TABLE #temp (acct_nbr INT,

ckid INT,

stat varchar(10),

Grp int);

INSERT INTO #temp (acct_nbr, ckid, stat)

SELECT 1, 101 ,'Open' UNION ALL

SELECT 1, 102 ,'Open' UNION ALL

SELECT 1, 103 ,'Open' UNION ALL

SELECT 1, 104 ,'Void' UNION ALL

SELECT 1, 105 ,'Void' UNION ALL

SELECT 1, 106 ,'Open' UNION ALL

SELECT 1, 107 ,'Open' UNION ALL

SELECT 1, 108 ,'Open' UNION ALL

SELECT 1, 109 ,'Void' UNION ALL

SELECT 1, 110 ,'Open' UNION ALL

SELECT 2, 101 ,'Open' UNION ALL

SELECT 2, 102 ,'Open' UNION ALL

SELECT 2, 103 ,'Open' UNION ALL

SELECT 2, 104 ,'Void' UNION ALL

SELECT 3, 105 ,'Void' UNION ALL

SELECT 3, 106 ,'Open' UNION ALL

SELECT 3, 107 ,'Open' UNION ALL

SELECT 4, 108 ,'Open' UNION ALL

SELECT 4, 109 ,'Void' UNION ALL

SELECT 4, 110 ,'Open';

-- expected output:

SELECT acct_nbr = 1, ckid = 101 , stat = 'Open', grp = 1 UNION ALL

SELECT 1, 102 ,'Open',1 UNION ALL

SELECT 1, 103 ,'Open',1 UNION ALL

SELECT 1, 104 ,'Void',2 UNION ALL

SELECT 1, 105 ,'Void',2 UNION ALL

SELECT 1, 106 ,'Open',3 UNION ALL

SELECT 1, 107 ,'Open',3 UNION ALL

SELECT 1, 108 ,'Open',3 UNION ALL

SELECT 1, 109 ,'Void',4 UNION ALL

SELECT 1, 110 ,'Open',5 UNION ALL

SELECT 2, 101 ,'Open',1 UNION ALL

SELECT 2, 102 ,'Open',1 UNION ALL

SELECT 2, 103 ,'Open',1 UNION ALL

SELECT 2, 104 ,'Void',2 UNION ALL

SELECT 3, 105 ,'Void',1 UNION ALL

SELECT 3, 106 ,'Open',2 UNION ALL

SELECT 3, 107 ,'Open',2 UNION ALL

SELECT 4, 108 ,'Open',1 UNION ALL

SELECT 4, 109 ,'Void',2 UNION ALL

SELECT 4, 110 ,'Open',3;