yup, that one did the trick! thanks mate.

you may be able to do it using "row_number" with "partition" by drive and "order" by date in descending order, then filter out those items that are not 1 in...

sorry just noticed that the department is not primary 😛

your update statement would be something like (untested code):

UPDATE Departments

SET DeptSeq = cte.deptSeq

FROM (

SELECT Department, ROW_NUMBER() OVER (Partition By Department Order By department) AS...

using this with more than 100 rows with the recursive option set to 0, makes it slower that using the while version of it 🙁

if you could provide us a more detailed description of the columns and formula, i think it would be easier to assist you.

sorry forgot to add the full script 😛

CREATE TABLE [dbo].[dfi_inv_master_list](

[acct_code] [int] IDENTITY(1,1) NOT NULL,

[part_no] [int] NULL,

[location] [int] NULL,

[description] [int] NULL,

[sku_no] [int] NULL,

[list_status] [int] NULL,

[pub_date] [int] NULL,

[pub_status] [int] NULL,

[succession_status] [int] NULL,

[void]...

changed the where a bit, tested with 100,000 rows:

SELECT * FROM (

select acct_code, part_no,location,description,sku_no,list_status

,pub_date,pub_status,succession_status,void,in_stock,avg_cost

,std_cost

,((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var

,(avg_cost - std_cost)*in_stock as extended_diff

,in_stock*avg_cost as extended_avg_cost

from dfi_inv_master_list with (NOLOCK)...

thanks a lot mate, that looks even better 😀