question

ojmp2001ojmp2001-0652 avatar image
0 Votes"
ojmp2001ojmp2001-0652 asked pituach edited

getting previous value thats not null

I have a table that has several columns I would like to get the previous value that is not null.
Create table #temp
(ID INT,
Testv Varchar(max)
)
Insert into #temp values (1,'')
Insert into #temp values (1,'abc')
Insert into #temp values (1,'def')
Insert into #temp values (1,'')
Insert into #temp values (1,'ijk')
Insert into #temp values (1,'')
Insert into #temp values (2,'xyz')
Insert into #temp values (2,'')
Insert into #temp values (2,'klm')
Insert into #temp values (2,'nop')

my desired output is as shown below
130052-image.png


sql-server-transact-sql
image.png (7.0 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I would like to get the previous value that is not null.

How do you define "previous"; data don't have a natural order? And in your test data there are no NULL values.



0 Votes 0 ·

Previous value is the value before the current row. So assuming you are at row 2, the previous value would be the value in row 1. if its row 5, the previous value would be the value in row 4 etc. Assuming that previous row is 4 and the row's value is blank/null, and row 3 is is also blank/null, I would like to get the value of row 2 which is not blank/null.
Lets assume in my table above that the blanks are null.
So am trying to get the last non null/blank value before a given row.

0 Votes 0 ·
pituach avatar image pituach ojmp2001ojmp2001-0652 ·

Good day,

before the current row.

before according to what order?!?

the previous value would be the value in row 1

Which row is "row 1" in your opinion, and how do think SQL Server can read your mind and opinion?!?

A table is a set of rows with no order! The server might return the data in different order each execution unless you have used "order by" explicitly!

It seems like you assume that the rows are ordered by the order that you INSERT them which is a huge mistake.

The source of the mistake is usually the fact that for very small tables when we use simple SELECT query then in most cases (but not guarantee!) this is the default behavior when the server uses a Sigle CPU.

0 Votes 0 ·
Show more comments
MelissaMa-msft avatar image
1 Vote"
MelissaMa-msft answered pituach edited

Hi @ojmp2001ojmp2001-0652,

Welcome to Microsoft Q&A!

After checking, below insert row is missing.

 Insert into #temp values (2,'hmn')

What is the version of your SQL Server?

If your version is SQL Server 2012 and later, please refer below and check whether it is helpful to you.

 Create table #temp
 (ID INT,
 Testv Varchar(max)
 )
 Insert into #temp values (1,'')
 Insert into #temp values (1,'abc')
 Insert into #temp values (1,'def')
 Insert into #temp values (1,'')
 Insert into #temp values (1,'ijk')
 Insert into #temp values (2,'hmn')
 Insert into #temp values (2,'xyz')
 Insert into #temp values (2,'')
 Insert into #temp values (2,'klm')
 Insert into #temp values (2,'nop')
    
 ;with cte as (
 select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)
 ,cte1 as (
 select *,max(Testv) over (partition by id,c) Testvv from (
 select *,c=count(Testv) over (partition by id order by rn)
 from cte )a)
 select ID,isnull(Testv,'')Testv,
 isnull(lag(Testvv) over (partition by id order by rn),'') desired
 from cte1 

Output:

 ID    Testv    desired
 1        
 1    abc    
 1    def    abc
 1        def
 1    ijk    def
 2    hmn    
 2    xyz    hmn
 2        xyz
 2    klm    xyz
 2    nop    klm

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you Melissa... However your solution does not solve the issue... what if there were 2 consecutive null values? I would get a value on the first non value but it will be blank in the 2nd one. if there was value on the 3rd row, it will be blank/null too.

0 Votes 0 ·

THIS IS VERY BAD!

your solution make no sense!

it is non-deterministic query which might return different results in different executions!

Seems like you forgot that a table (especially without clustered index) is not an ordered SET of rows.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

130293-image.png

In your query you are using: ROW_NUMBER() over (partition by id order by (select 1))
Which is basically a random order, which can lead to totally different result

For the sake of the discussion, since we are working with tiny tables, you can try to insert the rows in different order, and check the result which will be different.


0 Votes 0 ·
image.png (46.5 KiB)
pituach avatar image
0 Votes"
pituach answered pituach edited

DO NOT USE THE SULOTION PRESENTED BY MelissaMa-msft !

This solution is a non-deterministic query, which might return different results in different executions!

Seems like he forgot that a table (especially without clustered index) is not an ordered SET of rows.
https://docs.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15

130293-image.png

For the sake of the discussion, since we are working with tiny tables, we can try to insert the rows in different order, and check the result which will be different.

 DROP TABLE IF EXISTS #temp
 GO
 Create table #temp (ID INT, Testv Varchar(max) )
  Insert into #temp values (1,'abc')
  Insert into #temp values (1,'')
  Insert into #temp values (2,'nop')
  Insert into #temp values (1,'')
  Insert into #temp values (2,'klm')
  Insert into #temp values (1,'def')
  Insert into #temp values (2,'hmn')
  Insert into #temp values (2,'xyz')
  Insert into #temp values (1,'ijk')
  Insert into #temp values (2,'')
  GO
    
  ;with cte as (
  select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)
  ,cte1 as (
  select *,max(Testv) over (partition by id,c) Testvv from (
  select *,c=count(Testv) over (partition by id order by rn)
  from cte )a)
  select ID,isnull(Testv,'')Testv,
  isnull(lag(Testvv) over (partition by id order by rn),'') desired
  from cte1 

130312-image.png

 DROP TABLE IF EXISTS #temp
 GO
 Create table #temp (ID INT, Testv Varchar(max) )
  Insert into #temp values (1,'')
  Insert into #temp values (1,'abc')
  Insert into #temp values (1,'def')
  Insert into #temp values (1,'')
  Insert into #temp values (1,'ijk')
  Insert into #temp values (2,'hmn')
  Insert into #temp values (2,'xyz')
  Insert into #temp values (2,'')
  Insert into #temp values (2,'klm')
  Insert into #temp values (2,'nop')
  GO
    
  ;with cte as (
  select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)
  ,cte1 as (
  select *,max(Testv) over (partition by id,c) Testvv from (
  select *,c=count(Testv) over (partition by id order by rn)
  from cte )a)
  select ID,isnull(Testv,'')Testv,
  isnull(lag(Testvv) over (partition by id order by rn),'') desired
  from cte1 

130277-image.png



image.png (13.9 KiB)
image.png (14.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered pituach edited

Hi @ojmp2001ojmp2001-0652,

Thanks for your update.

I tried to add consecutive null values and found that my query was working.

Please refer below:

 Create table #temp
 (ID INT,
 Testv Varchar(max)
 )
 Insert into #temp values (1,'')
 Insert into #temp values (1,'abc')
 Insert into #temp values (1,'def')
 Insert into #temp values (1,'')
 Insert into #temp values (1,'')
 Insert into #temp values (1,'ijk')
 Insert into #temp values (2,'hmn')
 Insert into #temp values (2,'xyz')
 Insert into #temp values (2,'')
 Insert into #temp values (2,'')
 Insert into #temp values (2,'klm')
 Insert into #temp values (2,'nop')
    
  ;with cte as (
  select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by (select 1)) rn from #temp)
  ,cte1 as (
  select *,max(Testv) over (partition by id,c) Testvv from (
  select *,c=count(Testv) over (partition by id order by rn)
  from cte )a)
  select ID,isnull(Testv,'')Testv,
  isnull(lag(Testvv) over (partition by id order by rn),'') desired
  from cte1 

Output:
130553-output1.png

If you are doubt about the 'ROW_NUMBER() over (partition by id order by (select 1))' part, you could consider to add one identity column to fix the order.

Please refer below:

 Create table #temp1
 (
 RowID int identity(1,1),
 ID INT,
 Testv Varchar(max)
 )
 Insert into #temp1 (ID,Testv)values (1,'')
 Insert into #temp1 (ID,Testv)values (1,'abc')
 Insert into #temp1 (ID,Testv)values (1,'def')
 Insert into #temp1 (ID,Testv)values (1,'')
 Insert into #temp1 (ID,Testv)values (1,'')
 Insert into #temp1 (ID,Testv)values (1,'ijk')
 Insert into #temp1 (ID,Testv)values (2,'hmn')
 Insert into #temp1 (ID,Testv)values (2,'xyz')
 Insert into #temp1 (ID,Testv)values (2,'')
 Insert into #temp1 (ID,Testv)values (2,'')
 Insert into #temp1 (ID,Testv)values (2,'klm')
 Insert into #temp1 (ID,Testv)values (2,'nop')
    
  ;with cte as (
  select id,IIF(Testv='',NULL,Testv) Testv,ROW_NUMBER() over (partition by id order by RowID) rn from #temp1)
  ,cte1 as (
  select *,max(Testv) over (partition by id,c) Testvv from (
  select *,c=count(Testv) over (partition by id order by rn)
  from cte )a)
  select ID,isnull(Testv,'')Testv,
  isnull(lag(Testvv) over (partition by id order by rn),'') desired
  from cte1 

Output:

130581-output2.png
If above output is not expected, please provide more sample data and expected output.

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


output1.png (4.0 KiB)
output2.png (3.9 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you are doubt about the 'ROW_NUMBER() over (partition by id order by (select 1))' part, you could consider to add one identity column to fix the order.

The issue has nothing to do with "about the 'ROW_NUMBER()..."

You totally changed the question now, by adding the column RowID int identity(1,1)!

Adding this column solve the issue since it gives the identification for which row comes before (order of columns).

This is exactly what we ( @OlafHelper-2800 and myself ) speak about from the start. The table in the question does not have any identification column for the order of the rows, which mean it does not have any column which can be used for sorting the rows. There is no "before" or "after" if there is no order and in the table presented in the question in this thread. The original table does not have any column which can provide the information about the order of rows.

0 Votes 0 ·