number rows

I have wrote a query that returns product details for all orders that are at stage 6. Is there a way to number the product lines in each order no?!

Eg.
Order 1
Product 1 - 1
Product 2 - 2
Product 3 - 3

--counter restarts
Order 2
Product 1 - 1
Product 2 -2

thanks?If you are using SQL 2005, you could look into using the ROW_NUMBER function with the PARTITION BY option.|||Or you can just let the front end do it..or insert the rows into a temp table with an identity column then select from that