I am working with an order system that has two tables Order and OrderLine pretty standard stuff. I want to work out an order line number for the order lines with respect to the order e.g.
Orderid Orderlineid linenumber
1 1 1
2 2 1
2 3 2
3 4 1
4 5 1
4 6 2
The OrderLineId is an identity column. I don't want to store the line number as data in the database for two reasons. First there are already a great many existing orders and lines in the system and retrospectively adding the data is a headache I wish to avoid. Second if the user deletes a line then I would need to recalculate the line numbers for the whole order.
In SQL 2005 I can do this easy peasy using the ROW_NUMBER function.
Select Orderid, OrderLineid, ROW_NUMBER()
OVER(PARTITION BY Orderid ORDER BY Orderlineid) as LineNumber
SELECT x.Ranking, x.OrderId
FROM (SELECT (SELECT COUNT( DISTINCT t1.Orderid) FROM orderline t1 WHERE z.Orderid >= t1.Orderid)AS Ranking, z.orderid
FROM orderline z ) x
ORDER BY x.Ranking
You can use something like this:
select ol1.orderId, ol1.orderLineId, count(*) as lineNumber from orderLine ol1 inner join orderLine ol2 on ol1.orderId = ol2.orderId and ol1.orderLineId >= ol2.orderLineId group by ol1.orderId, ol1.orderLineId