SELECT Billnumber,Seq AS LineItem
FROM
(
SELECT Billnumber,ROW_NUMBER() OVER (PARTITION BY Billnumber ORDER BY (SELECT NULL)) AS Seq
FROM table
)t


if sql 2000 this is one way
CREATE TABLE #temp
(
ID int IDENTITY(1,1),
Billnumber int
)
INSERT #Temp (Billnumber)
SELECT Billnumber
FROM table
ORDER BY Billnumber

SELECT Billnumber,
COALESCE((SELECT COUNT(*) FROM #Temp WHERE Billnumber = t.Billnumber AND ID < t.ID),0)+1 AS LineItem
FROM #temp t

DROP Table #temp

quote:

---

Originally posted by sreeram84n

select BillNumber,
(select COUNT(*) from yourTable where yourTable .BillNumber<=t.BillNumber) as rowid from yourTable t

---