I have a sample table below. From that table #A I need the as shown below. Basically I wanted to get the data from first not zero row. If after that any zero occurs no issue on that.
Create table #A
(
[Data] int
)
insert into #A values(0),
(0),
(0),
(0),
(0),
(23),
(524),
(723),
(0),
(89),
(23),
(4),
(51),
(0),
(0)
Select * from #A
Required Output :-
Data
23
524
723
0
89
23
4
51
0
0
I tried below script but my data column iteself getting sorted. Which is not working properly.
Select *
from
(
Select *
,
ROW_NUMBER() over(order by data) Rn
from #A
) t
>Solution :
As @Larnu mentioned: Without a proper sequence, there is NO GTD of the desired order.
Take a peek at Unordered results in SQL
Notice I added an ID which could be an IDENTITY or even a datetime stamp
Example
Create table #A
(
id int,[Data] int
)
insert into #A values(1,0),
(2,0),
(3,0),
(4,0),
(5,0),
(6,23),
(7,524),
(8,723),
(9,0),
(10,89),
(11,23),
(12,4),
(13,51),
(14,0),
(15,0)
Option 1:
Select *
From #A
Where ID>= (Select top 1 id From #A where data<>0 Order By ID )
Order by ID
Option 2:
Select id
,data
From (
Select *
,Flg = sum(case when Data<>0 then 1 else 0 end) over (order by id)
from #A
) A
Where Flg>0
Order By ID
Results
id data
6 23
7 524
8 723
9 0
10 89
11 23
12 4
13 51
14 0
15 0