SQL Query help

Question

SQL Query help

mikesql710

Old Hand

Points: 342
More actions
October 17, 2023 at 8:51 pm

#4309982
Hi,
How can Insert the same record until I find the next value in SQL? Here is my sample code/data.
```
DECLARE  @test as table
(
    id int IDENTITY(1,1),
    code varchar(20),
result varchar(20)
);

INSERT INTO @test (code,result)
VALUES
('R235','Good'),
('R236',''),
('R236',''),
('R235','Failed'),
('R238',''),
('R239','Verified'),
('R239',''),
('R235','')

SELECT * FROM @test t
```
Here what I want.
Final output
id,code,result
1,R235,Good
2,R236,Good
3,R236,Good
4,R235,Failed
5,R238,Failed
6,R239,Verified
7,R239,Verified
8,R235,Verified
Thanks for help!

Viewing 3 posts - 1 through 2 (of 2 total)

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

Steve Collins SSCrazy Points: 2175 More actions · Answer 1

It seems like you're looking for an UPDATE statement

/* select using OUTER APPLY and TOP(1) */
select t.id, t.code, t.result, prev.result prev_result, 
       coalesce(nullif(t.result, ''), prev.result) result 
from @test t
     outer apply (select top(1) tt.result
                  from @test tt
                  where tt.id<t.id
                        and len(tt.result)>0
                  order by tt.id desc) prev(result);

/* update after inserted */
update t
set result=coalesce(nullif(t.result, ''), prev.result) 
from @test t
     outer apply (select top(1) tt.result
                  from @test tt
                  where tt.id<t.id
                        and tt.result<>''
                  order by tt.id desc) prev(result)
where t.result='';

select * from @test;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

drew.allen SSC Guru Points: 76997 More actions · Answer 2

Generally, an approach that uses windowed functions is going to perform better than one using CROSS/OUTER APPLY, because it's going to have to read the table fewer times. The following approach uses windowed functions. The formula is a bit complicated, but it's actually fairly easy if you break it down into parts.

We want the last non-blank value, but it's easier to work with non-null values, so we're converting blanks to nulls.
- NULLIF(t.result, '')
LAST_VALUE() gives the last value regardless of whether it is null, so we are using MAX() instead.
We want the value of one field (result) based on the max of another field (id), so we are concatenating them.
They are different data types, so we're converting them both to binary.
We're using SUBSTRING() to get the part of the max value that corresponds to the field we want.
- We're converting that substring back to varchar.

WITH smear AS
(
    SELECT *
         , CAST(SUBSTRING(MAX(CAST(t.id AS BINARY(5)) + CAST(NULLIF(t.result, '') AS BINARY(20))) OVER(ORDER BY t.id ROWS UNBOUNDED PRECEDING), 6, 20) AS VARCHAR(20)) AS PrevValue
    FROM @test2 AS t
)
UPDATE smear
SET result = PrevValue;

Here is a comparison of the reads for the two queries.

-- Steve's query
Table '#AA53CFDE'. Scan count 2, logical reads 7, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 2, logical reads 40, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

-- Drew's query
Table '#AB47F417'. Scan count 1, logical reads 9, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

Drew

Edited to correct the name of the author of the other solution.

This reply was modified 1 year, 2 months ago by drew.allen.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA