MS SQL problem: fill down variable number of rows from last known value.

Using Microsoft query analyser SQL version 8, I believe.

Got a SQL table with a column that has NULLS in it. What I want to do is fill down the value in the row above based on a condition in another column (recordtype). Came up with the following code but unfortunately it only fills in the record directly below. Not those further down (variable number of NULL rows). Any idea how to get these to fill down? Suspect the answer may lie with block cursors but 1) I'm told they're slow (I have 2 and 1/2 million records in this table) and 2) I've never used cursors before!

________________

Update dbo.NSTS

Set dbo.NSTS.Number = NSTS_1.Number

FROM dbo.NSTS LEFT OUTER JOIN
dbo.NSTS AS NSTS_1 ON dbo.NSTS.[Id] = ((NSTS_1.[Id])+1)

WHERE dbo.NSTS.RecordType like '101' AND (dbo.NSTS.Number is null or dbo.NSTS.Number like '')

________________

Example of data table here:

ID RecordType Number
1 100 4325273182
2 101 NULL
3 101 NULL
4 101 NULL
5 100 4342684020
6 100 4342696800
7 100 4304028782
8 100 4305914743
9 101 NULL
10 101 NULL
11 100 4328179942
12 101 NULL
13 101 NULL
14 101 NULL
15 101 NULL
16 100 4329313804
17 100 4329874164
18 101 NULL
19 100 4330053764
20 101 NULL
_________________

I did get a cursor loop working but can only retrieve 1 row and (try as I might with the so called help examples) can't seem to pull down 2 rows at a time. Just can't seem to get it to accept this command at all: SQLSetStmtAttr(hStmt, SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER) 2 ,0).

Help would be very much appreciated!not sure I understand...when you say fill...fill with what?

Also the order of data in a database is meaningless, so what does "next" mean?|||Beg your pardon. Can't see the wood for the trees.

In this case the data is in a strictly sequential order as defined by the ID field. So, first has ID = 1 and next = current ID + 1.

Data comes from another source in a rather weird format which I won't bother you with. Each row with recordtype of 100 has latest data. If something has changed in the past then there will be another row with a recordtype of 101 and with the detail of the value that changed in the appropriate column. If a column hasn't changed then a NULL will be present. In this instance, I want to take the value in that column (Number in this instance) from the row that has an ID of ID-1 (the newer record.) It should be noted that it is possible to have NULL values in the number field so you could have a record type of 100 with a NULL value. In this case it needs to be left as NULL.

Have I explained my dilema appropriately? I've actually just tried doing this with a while (count(recordtype) ... where Number is null >0) type statement before I realised there actually were valid nulls in the Number column! :eek:|||Beg your pardon. Can't see the wood for the trees.

Guess I'm not smart enough to help on this.

Good Luck|||This is actually a good puzzle, but I don't have enough time to solve it before my next meeting. If nobody else solves it before then, I'll do it tonight.

-PatP|||Sorry I didn't get this one last night. It was still a fine puzzle!CREATE TABLE #NSTS (
id INT NOT NULL
, record INT NOT NULL
, number BIGINT NULL
)

INSERT INTO #NSTS (id, record, number)
SELECT 1, 100, 4325273182 UNION
SELECT 2, 101, NULL UNION
SELECT 3, 101, NULL UNION
SELECT 4, 101, NULL UNION
SELECT 5, 100, 4342684020 UNION
SELECT 6, 100, 4342696800 UNION
SELECT 7, 100, 4304028782 UNION
SELECT 8, 100, 4305914743 UNION
SELECT 9, 101, NULL UNION
SELECT 10, 101, NULL UNION
SELECT 11, 100, 4328179942 UNION
SELECT 12, 101, NULL UNION
SELECT 13, 101, NULL UNION
SELECT 14, 101, NULL UNION
SELECT 15, 101, NULL UNION
SELECT 16, 100, 4329313804 UNION
SELECT 17, 100, 4329874164 UNION
SELECT 18, 101, NULL UNION
SELECT 19, 100, 4330053764 UNION
SELECT 20, 101, NULL

SELECT a.id, a.record, (SELECT b.number
FROM #NSTS AS b
WHERE b.id = (SELECT Max(c.id)
FROM #NSTS AS c
WHERE c.number IS NOT NULL
AND c.id BETWEEN (SELECT Max(d.id)
FROM #NSTS AS d
WHERE d.id <= a.id
AND d.record = (SELECT Min(e.record)
FROM #NSTS AS e)) AND a.id))
FROM #NSTS AS a

DROP TABLE #NSTS
-PatP|||Wow. What's it like in your mind? All flashing cogs and some sort of weird matter distortion device that bends reality into the kind of shapes normally only encountered on class A drugs?

Many thanks for this. Much more elegant than my attempt. Actually got that to work in the end by using:

________

While (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '101'))) > (select count(RecordType) from NSTS where ((Number is null) and (RecordType like '100')))

Update NSTS

Set NSTS.Number = Tbl_NSTS_PracticeDownloads_1.Number

FROM NSTS LEFT OUTER JOIN
NSTS AS Tbl_NSTS_PracticeDownloads_1 ON NSTS.[Id] = ((Tbl_NSTS_PracticeDownloads_1.[Id])+1)

WHERE NSTS.RecordType like '101' AND (NSTS.Number is null or NSTS.Number like '')

_________

Question now is: which will crunch fastest with 2/1/2 million records? Guess I'll have to find out!

Once again, many thanks for your help on this!