John Hunter John Hunter - 5 months ago 18
SQL Question

How do I Compute an Order Line Number in SQL 2000

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
FROM OrderLine


Is there anyway I can do this in SQL 2000?

The closest I found was a ranking function (see below) but this counts orders not lines.

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

Answer

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
Comments