if you're on sql 2005 or above you can use likeSELECT Billnumber,Seq AS LineItemFROM(SELECT Billnumber,ROW_NUMBER() OVER (PARTITION BY Billnumber ORDER BY (SELECT NULL)) AS SeqFROM table)tif sql 2000 this is one wayCREATE TABLE #temp(ID int IDENTITY(1,1),Billnumber int)INSERT #Temp (Billnumber)SELECT BillnumberFROM tableORDER BY BillnumberSELECT Billnumber,COALESCE((SELECT COUNT(*) FROM #Temp WHERE Billnumber = t.Billnumber AND ID < t.ID),0)+1 AS LineItemFROM #temp tDROP Table #temp
------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/