How do I Compute an Order Line Number in SQL 2000

Question

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 1

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

Answer 2

IMHO, calculating it every time you need it may end up being more of a headache than it's worth. And while it may be a pain to update the historical line numbers and save it to the database, you'd only be doing that once... and then would just need to write code to automate it later.

To handle the deletes, you'd just need to add code into the existing delete procedure and have it recalculate the line numbers for it.

Answer 3

You may want to check out this article.

Answer 4

An alternative would be to insert your data (without line number) into a #temp table where you add a column with identity(1,1) and then selects everything from the #temp table.