Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Ignore zeros till you get the first non zero value(Without Ranking Functions)

This puzzle is bit different puzzle. In this puzzle you have to ignore all consecutive zeros till the time you get a non zero value. The catch is you cannot use a ranking function

For details please check out the sample input and the expected output below-

Sample Input

VALUE
0
0
0
0
1
2
0
3
0

Expected Output

Value
1
2
0
3
0

Rules/Restrictions

  • The solution should be should use “SELECT” statement or a CTE.
  • Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script | use below script to create table & insert some sample data


--

CREATE TABLE sqlpuzl
(
	value int
)
GO

INSERT INTO sqlpuzl VALUES 
(0),
(0),
(0),
(0),
(1),
(2),
(0),
(3),
(0)

--

Solution 1


--

SELECT Value 
       FROM 
sqlpuzl 
        WHERE  %%PhysLoc%% >= 
        (
	   SELECT MIN(%%PhysLoc%%) FROM sqlpuzl WHERE VALUE > 0
        )

--


--Note : Don't use this method on production.

Add a comment if you have a solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com

Advertisements