Clever Geek Handbook

How to calculate order line number in SQL 2000

I am working with an ordering system that has two tables Order and OrderLine of pretty standard stuff. I want to work out the order line number for order lines relative to order, for example.

Orderidid ​​linenumber
1 1 1
2 2 1
2 3 2
3 4 1
4 5 1
4 6 2

OrderLineId is the identity column. I don't want to store the row number as data in the database for two reasons. First, there are already many existing orders and lines in the system, and in retrospect adding data is a headache that I want to avoid. Second, if the user deletes a line, I will need to recalculate the line numbers for the whole order.

In SQL 2005 I can do it peasy easily 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've found is a ranking function (see below), but this is a counting of orders not rows.

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
+1


source to share


3 answers


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
+3


source


IMHO figuring it out every time you need it can end up being more of a headache than it's worth. And while it can be painful to update the historical line numbers and store them in the database, you only do it once ... and then you just need to write the automation code later.



To deal with the deletion, you just need to add code to the existing delete routine and recalculate the line numbers for it.

0


source


An alternative might be to insert your data (without row number) into the #temp table where you add the column with id (1,1) and then select everything from the #temp table.

0


source






More articles:


All Articles