need to link to previous row - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

need to link to previous row

Author

Topic

yhassan
Starting Member

8 Posts

Posted - 2008-10-09 : 06:26:51

Hi, I hope someone can help with the following problem:

I need to create a column (C) which calculates the difference between the numbers in col B, based on a criteria in col A being met. For example:

A B C
apple 2
apple 4 2
orange 3
banana 5
banana 7 2
banana 3 -4
orange 2

The criteria is that col A must equal the value in the previous row. As long as this is true, then the difference between col B and the previous row value in col B is calculated and put in a new col C.

I hope this makes sense. Any help would be greatly appreciated!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 06:28:39

There are numerous examples of this here at SQLTeam.
Search first, ask later.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-09 : 06:30:16

Hmmm.. A 30 second search made you find this article
http://www.sqlteam.com/article/joining-to-the-next-sequential-row

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-09 : 06:32:01

[code]SELECT t.A,t.B,t.C-COALESCE(t1.C,0)
FROM Table t
OUTER APPLY (SELECT TOP 1 B
FROM Table
WHERE A=t.A
AND ID < t.ID
ORDER BY ID DESC)t1[/code]

hope you have unique valued column ID in your table.

yhassan
Starting Member

8 Posts

Posted - 2008-10-09 : 07:02:14

Thanks all for the replies, im sure this will solve my problem!

PS apologies, I'll search first next time!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources