Getting a value from a previous row.

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

Transact-SQL (2008)

Getting a value from a previous row.

Author

Topic

akira220984
Starting Member

13 Posts

Posted - 2010-01-25 : 05:23:03

Good Morning (Evening?) everyone.

I have a question.

Imagine a table that looks like this:

Column1 Column2
1. 5 14
2. 4 9
3. 3 5
4. 2 2

Where column2 is equal to column1.row(n)+ column2.row(n) (column2 has a default of 0) starting from bottom of the table, and working upwards.

If I update row 2 column1, I need to get the value from row 3, column2 (the previous row) and use it to update row 2 and row 1.

Is there a way of getting to this value?

Sorry if this seems overly simplistic, but I cannot think how to do it.

Any other info needed let me know.

Thanks,

Ric

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-25 : 05:31:16

What column is used to define order of records; define which is at "bottom"?

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

akira220984
Starting Member

13 Posts

Posted - 2010-01-25 : 05:38:57

Hi Peso,

I have written the following:

declare @lastBalance money
set @lastBalance = (select top(1) runningbalance from ledgertestrs
where ledgertestrs.longtransactiondate <= ###CURRENT.LONGTRANSACTIONDATE###
order by longtransactiondate desc)

I am ordering by date, so latest date to oldest date. I have just realised that I need to select a record whose date is <= (but just the last, hence the top(1)) the updated record.
Now I am looking for a way to amend the line above (###CURRENT.LONGTRANSACTIONDATE###). What is now needed is a way to find the longtransactiondate of the current record! :)

Edging slightly closer, I think lol

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:15:41

[code]select t.*,t.column1+ coalesce(t1.runningbalance ,0)
from yourtable t
outer apply (select top(1) runningbalance from ledgertestrs
where ledgertestrs.longtransactiondate <=t.longtransactiondate
order by longtransactiondate desc)t1
[/code]

akira220984
Starting Member

13 Posts

Posted - 2010-01-25 : 06:35:50

Hi visakh16,

Pardon my ignorance, but I don't quite understood your post :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-25 : 06:37:56