How to get First not zero value in table column in sql server-CodePudding

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

CodePudding user response：

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