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(
id integer
,insert_order integer
,value integer
)
INSERT INTO @table
SELECT 1,1,2
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
SELECT
id
,value
,(
SELECT
TOP 1 value
FROM
@table T2
WHERE
T2.id = T1.id
AND
T2.insert_order < T1.insert_order
ORDER BY
T2.insert_order desc
) AS previous_value
FROM
@table T1
ORDER BY
T1.id
,T1.insert_order
Previous Value... Is there a "better" way. DDL inc
Posted - 05/10/2007 : 07:13:35
