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

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.

Count Item function

Hi,

I need to count the number of rows in my Item table. The following statement gives me the number i.e. 200.

Select Count(*) As Counter
From Item
Group By Id

How can I number each individual row so that the row will have a number next to it i.e.

Select Count(*) As Counter,[Count Statement] as Number of the Row
From Item
Group By Id

Thanks

Is this what you want?

SELECT Row_NUMBER() OVER(Order by a.id) as series_No, a.id, b.myCount

FROM items a

LEFT JOIN (SELECT COUNT(*) as myCount, id

FROM items

group by id) b ON a.id=b.id

|||

I am not sure, I get the following error.

Server: Msg 195, Level 15, State 10, Line 1
'Row_NUMBER' is not a recognized function name.
Server: Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near 'b'.

|||

Row_Number() is a new function in SQL Server 2005.

Try this one:

SELECT (select count(*) from items as t2

where t2.items <= t1.items )+1 as series_No, t1.id

FROM items t1

ORDER BY t1.items

|||Thanks, that helped.|||You should actually do this on the client-side where it is easier and it will perform better. Simply return the rows in a sorted manner and then number them on the client side.