Data from Previous Row

Question

Post reply

Data from Previous Row

Luke Ireland

Old Hand

Points: 300
More actions
September 14, 2006 at 5:27 am

#367424

I have a table containing the following (example) data
A B
20000 0
NULL 1
NULL 1
20001 0
NULL 1
NULL 1
NULL 1
20002 0
NULL 1
20003 0
NULL 1
What I need to do is copy the value from the previous row when Column A is NULL so this becomes
A B
20000 0
20000 1
20000 1
20001 0
20001 1
20001 1
20001 1
20002 0
20002 1
20003 0
20003 1
It is a relatively big table (180,000 rows) so I guess some kind of recursive function is required here.
Any ideas would be welcome. There is other data in columns C on onwards so I need to keep the integrity of those columns too
Regards
Luke

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

WHich row you consider previous?

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 2

Working back from current row back until it finds a column A value that isn't NULL and use that value. Working down from Row 2 this will be previous row?

Luke

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Which one is Row 2?

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 4

In my example the first three rows are:

20000 0

NULL 1

Luke

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Why these?

Why not those:

20002 0

NULL 1

20003 0

?

What is defining the order of rows in your table?

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 6

The data is sales order data from a legacy system. It comes in as a row with the order header info (the 20000 number is the customer number) then the next rows are the line data then it goes to the next customer (e.g. 20001). I need to link the customer number onto those line rows so that I can then create a new table with the customer number and line details on the same row. From this I can generate sequential order numbers and import it into the new system.

Thanks for your help

Luke

Sergiy SSC Guru Points: 110208 More actions · Answer 7

OK.

There are too many words. Lets keep it simple.

Write a SELECT to retrieve your "Row 2".

Can you do it?

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 8

There is nothing on Row 2 that identifies it as such or links it to Row 1, that is my problem.

Sergiy SSC Guru Points: 110208 More actions · Answer 9

So, you have nothing to do with this.

Sorry.

You must have lost some data when copied data from legacy system. Something you did not noticed made those rows consequtive. And now that "something" is missing.

Go back, find the missing link and copy data properly.

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 10

Unfortunately that is the way the data is held in that old system. It runs very slowly because of the way it holds data which of course is one of the reasons the client is changing.

I can do what I need to do in Excel or VB, just wondered if it was possible to do it quickly in T-SQL.

Anyway thanks for having a think about it

Luke

Sergiy SSC Guru Points: 110208 More actions · Answer 11

I'm afraid VB won't help.

Your rows will be shuffled after 1st update. And you could never restore the original order.

You definetely wrong about old system. It DOES hold the order somewhere, you just don't know where. Otherwise it would not work, even slowly.

What you can do is insert you data from old system into straight into a table with IDENTITY column. Then, you you are lucky, order of IDs in your new table will repeat the order in old system.

_____________
Code for TallyGenerator

Luke Ireland Old Hand Points: 300 More actions · Answer 12

This is the way the data is on the old system. I have no idea how it works but last time I worked with it the data was grafted into shape by hand.

Your answer has inspired a slightly manual way of doing this in SQL

I have added an Identity as ID to the table then repeatedly run the following until all rows updated.

SELECT * INTO #holding FROM test_table

UPDATE test_table

SET colA = (SELECT colA FROM #holding WHERE #holding.id=test_table.id-1) WHERE test_table.colA IS NULL

DROP TABLE #holding

Not the most elegant solution but it works. Thanks for the inspiration

Luke

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Your way is quite dangerous. Rows may be not in order already.

Set up order right on data arrival:

SELECT Identity(int, 1,1) as ID, *

INTO test_table

FROM OPENQUERY(...) -- or whatever you use to get the data from old system.

_____________
Code for TallyGenerator

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 14

This solution assumes that you have no way to order the data and the order that you get from Source is right.

1.Load the data in the table( PreviousRow) it to Temp table with identify(Can be permanant temp table as well)

Select identity(int,1,1) as IDCol, * into #tmp

from PreviousRow

2.Use the Select query to validate the result and if you are satisfied change the Select to Update

Select #tmp.IDCol,#tmp.ColA,#tmp.ColB ,Range.ColA

from #tmp INNER JOIN (

select Starts.IDCol,Starts.ColA,

isnull((select Top 1 Ends.IDCol from #tmp Ends where Ends.ColA is not null

and Ends.IDCol > Starts.IDCol ),(SElect max(IDCol) from #tmp)) as EndIDCol

from #tmp Starts

where Starts.ColA is not null) Range

on #tmp.IDCol between Range.IDCol and Range.EndIDCol