SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Previous Value... Is there a "better" way. DDL inc
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

uberman
Posting Yak Master

158 Posts

Posted - 05/10/2007 :  07:13:35  Show Profile  Reply with Quote
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

Edited by - uberman on 05/10/2007 07:20:24

chiragkhabaria
Flowing Fount of Yak Knowledge

India
1907 Posts

Posted - 05/10/2007 :  07:52:13  Show Profile  Visit chiragkhabaria's Homepage  Send chiragkhabaria a Yahoo! Message  Reply with Quote
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

India
319 Posts

Posted - 05/10/2007 :  07:59:23  Show Profile  Reply with Quote
Provided the order is in sequence like 1,2,3 for each id

Edited by - pbguy on 05/10/2007 08:01:16
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 05/10/2007 :  08:55:27  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 05/10/2007 08:56:57
Go to Top of Page

uberman
Posting Yak Master

158 Posts

Posted - 05/10/2007 :  09:08:19  Show Profile  Reply with Quote
Thanks... and Thanks!

When posting I always try and ensure I dont end up on the "Twit List"!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000