QUERY HELP
-
Hello.
I need help with the following query:
declare @T table (
ID int not null primary key
, VAL int null
);
insert into @T
(ID, VAL)
values
(1, 1000), (2, null), (3, null), (4, null)
, (5, 2000), (6, null), (7, null), (8, null)
;
select T.*
, (
select S.VAL
from @T S
where S.ID = (
select min(SS.ID)
from @T SS
where SS.ID > T.ID and T.VAL is not null
)
) [NEW_VAL]
from @T T
--result:
--ID VAL NEW_VAL
--1 1000 NULL
--2 NULL NULL
--3 NULL NULL
--4 NULL NULL
--5 2000 NULL
--6 NULL NULL
--7 NULL NULL
--8 NULL NULL
--Needed result:
--ID VAL NEW_VAL
--1 1000 1000
--2 NULL 1000
--3 NULL 1000
--4 NULL 1000
--5 2000 2000
--6 NULL 2000
--7 NULL 2000
--8 NULL 2000
Thank You
-
Here's your original data as a Temp Table to persist the test data to make "playing" easier...
DROP TABLE IF EXISTS #Temp;
GO
CREATE TABLE #Temp
(
ID INT NOT NULL PRIMARY KEY
,VAL INT NULL
)
;
INSERT INTO #Temp WITH (TABLOCK)
(ID, VAL)
VALUES (1,1000)
,(2,null)
,(3,null)
,(4,null)
,(5,2000)
,(6,null)
,(7,null)
,(8,null)
;Here's one solution for use prior to SQL Server 2017 without using a "Quirky Update" to do the "Data Smear". SQL Server 2017 and up has a nice "First_Value" function that would have made this a bit easier.
SELECT ID
,Val
,New_Val = v.LastVal
FROM #Temp t
CROSS APPLY
(
SELECT TOP 1
ca.VAL
FROM #Temp ca
WHERE ca.Val IS NOT NULL
AND ca.ID <= t.ID
ORDER BY ID DESC
)v(LastVal)
ORDER BY t.ID
;Here are the results...
Also, please use the code window for code and results for future posts. It'll make the code easier to read and allows for space-indents.
I also believe that LAG might be able to make this work but I ran out of time for this post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you very much
-
Thanks for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I believe that this code is faster. It's based on code by Itzik Ben-Gan.
SELECT ID
, t.VAL
, CAST(SUBSTRING(MAX(CAST(ID AS BINARY(5)) + CAST(t.VAL AS BINARY(5))) OVER(ORDER BY t.ID ROWS UNBOUNDED PRECEDING), 6, 5) AS INT) AS New_Val
FROM #Temp AS tHere is the comparison. (Table name edited to remove underscores.)
/* Jeff's version. */
Table '#Temp_____00000001087D'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
/* My version. */
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#Temp_____00000001087D'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
I agree with Drew... the code he posted does a single scan and not a scan with a wad of seeks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I just did a deep dive on that code. ANSI NULLs are the key there! Really cool code! Thanks, again, Drew. Do you have a link where Itzik provides this great and really simple idea?
Note to self: Today, I learned something new AND useful! Awesome data-smear method.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
I just did a deep dive on that code. ANSI NULLs are the key there! Really cool code! Thanks, again, Drew. Do you have a link where Itzik provides this great and really simple idea?
Note to self: Today, I learned something new AND useful! Awesome data-smear method.
The Last non NULL does use this as one of its methods, but I don't think that's where I originally saw it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Aye. Thanks for the link. The solution that you posted is even simpler than either of those two and now I understand about why you say it's not where you think you saw it.
Either way, once I took a look at the breakdown in the code, it incredibly elegant and simple at the same time. Thanks, again, Drew.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Drew showed me his wonderful code to do this same thing on another thread. I did a deep dive on it and the concept is wonderfully simple and it solved the problem with using MAX() OVER that I didn't solve in one of my other attempts.
To demonstrate how insanely quick Drew's code is, I "Modenized" the sample data. Here's the code. For those unfamiliar with "fnTally", you can get that at the similarly named link in my signature line at the bottom of this post or use one of your own to replace it (unless it has a loop or rCTE or other form of RBAR in it).
--PREFORMANCE TEST. Absolutely mind blowing how fast it is.
--===== Drop the Temp Tables to make reruns easier.
DROP TABLE IF EXISTS #Temp,#Result;
GO
CREATE TABLE #Temp
(
ID INT NOT NULL PRIMARY KEY
,VAL INT NULL
)
;
--===== Create the Test Data in #Temp
WITH
cteGenRareVal AS
(
SELECT ID = ABS(CRYPT_GEN_RANDOM(4)%1000000)+1 --Random ID's 1 to 1 Million
,Val = ABS(CRYPT_GEN_RANDOM(4)%1000000000)+1 --Random Val's 1 to 1 Billion
FROM dbo.fnTally(1,1000)
)
,cteGenID AS
(
SELECT ID = t.N
FROM dbo.fnTally(1,1000000)t
)
INSERT INTO #Temp WITH (TABLOCK)
(ID, VAL)
SELECT gid.ID
,rv.Val
FROM cteGenID gid
LEFT JOIN cteGenRareVal rv
ON gid.ID = rv.ID
ORDER BY gid.ID
;
--===== Solve the problem and dump the results to a Temp Table
-- to take display time out of the picture.
SET STATISTICS TIME ON
;
SELECT ID
,t.VAL
,NewVal = CAST(SUBSTRING(
MAX(CAST(ID AS BINARY(5)) + CAST(t.VAL AS BINARY(5)))
OVER(ORDER BY t.ID ROWS UNBOUNDED PRECEDING)
,6,5) AS INT)
INTO #Result
FROM #Temp AS t
ORDER BY ID
;
SET STATISTICS TIME OFF
;
GOHere are the STATISTICS from my box... insanely fast code for what it does. Haven't tried the "Quirky Update" method for this but I'm thinking it'll be real close to a dead-heat.
(1000000 rows affected)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 890 ms, elapsed time = 880 ms.
Warning: Null value is eliminated by an aggregate or other SET operation.
(1000000 rows affected)--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply