Count Item Number

Hello;

Which is the easy way to do this:

I have 2 tables:Work_Order_Header and Work_Order_Detail.

I need to generate the Item Number from 1 to "qty of items" when I make the JOIN on WorkOrderNumber.

Example:

WorkOrderNumber WorkOrderItem WorkOrderAmt

122 1 10.00

122 2 15.25

122 3 24.37

How I generate the WorkOrderItem using a select with a JOIN on the 2 tables?

if you are working on SQL Server 2005, you can use the following (samples tables from Northwind database, as you provided no DDL and sample data)

SELECT ROW_NUMBER() OVER (PARTITION BY O.OrderId order by O.OrderDate),O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||Thank you Jens, but I am working with SQL 2000 I forgot to mentioned.|||A sample you be something like:

SELECT (SELECT COUNT(*) FROM [Order details] OD2 WHERE OD2.orderID = O.OrderID AND OD2.ProductID >=OD.ProductID) AS Number ,O.*,OD.* FROm

Orders O

INNER JOIN [Order details] OD

ON O.orderid = od.OrderID

WHERE O.orderid <=10251

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

|||Did that solve your problem ?sql