I have an table of items and quantities, and I need to find for any item what was the previous quantity (changing the DDL for the table and possibly storing the previous value is NOT currently an option)
I have narrowed this down to the demo code below.
My concern is that as the number of entries in the table grows, the use of the subquery will become ... slow.
Is there a "better" way of doing this in SQL Server 2000...?
DECLARE @table table(
INSERT INTO @table
UNION SELECT 2,1,3
UNION SELECT 2,2,1
UNION SELECT 1,2,4
UNION SELECT 2,3,5
UNION SELECT 2,4,10
SELECT * FROM @table ORDER BY id, insert_order
TOP 1 value
T2.id = T1.id
T2.insert_order < T1.insert_order
) AS previous_value