Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Previous Value... Is there a "better" way. DDL inc

Author  Topic 

uberman
Posting Yak Master

159 Posts

Posted - 2007-05-10 : 07:13:35
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

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-05-10 : 07:52:13
How about somthing like this???


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 T1.ID, T1.Value, T2.Value
From
@table T1 Left outer Join @table T2
On
T2.id = T1.id
AND
T2.insert_order =T1.insert_order -1
Order by 1


Chirag

http://chirikworld.blogspot.com/
Go to Top of Page

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-10 : 07:59:23
Provided the order is in sequence like 1,2,3 for each id
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-10 : 08:55:27
As long as you have indexes, you should be fine. what you've written is pretty much the standard way to handle it.

by the way -- good question, nice job with providing info and sample code. Very nice!

- Jeff
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

uberman
Posting Yak Master

159 Posts

Posted - 2007-05-10 : 09:08:19
Thanks... and Thanks!

When posting I always try and ensure I dont end up on the "Twit List"!
Go to Top of Page
   

- Advertisement -