CODEWITHSUNDEEP
sqlsql-servert-sql
Piyusha
Piyusha

Reputation: 23

How to get First not zero value in table column in SQL Server

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 itself getting sorted. Which is not working properly.

SELECT *
FROM 
    (SELECT 
         *,
         ROW_NUMBER() OVER (ORDER BY data) Rn
     FROM
         #A) t

Upvotes: 0

Views: 1820

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81950

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

Upvotes: 2

Related Questions