SQL Puzzle: SQL Advance Query - Skip the first set of Zeros

SQL Puzzle: SQL Advance Query – Skip the first set of Zeros

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output to skip the zeros before the non-zero number.
I asked this question many times in an interview. Look like very simple, but not a single DB Developer gave me correct answer till date.

Input data:

num

------

Expected Output:

num

------

Create a sample table with data:

CREATE TABLE tbl_ids

(

num int

)

INSERT INTO tbl_ids VALUES

(0),(0),(0)

,(0),(1),(2)

,(0),(3),(4)

Solution:

SELECT num

FROM tbl_ids

WHERE %%PhysLoc%% >=

(

SELECT MIN(%%PhysLoc%%) FROM tbl_ids WHERE num > 0

)

Please try the different solution for this puzzle and share it via comment...

Oct 14, 2017Anvesh Patel

Comments: 4

Dinesh.IS

October 16, 2017 at 7:57 am

Declare @Values As VarChar(Max),@XML As XML;
Set @Values=”
Select @Values+=+’,’+Convert(VarChar(50),Num) From (Select Num From TBL_IDS) A
Set @Values=Right(@Values,PatIndex(‘%[1-9]%’,@Values)-1)
Set @XML=”+Replace(@Values,’,’,”)+”
Select r.value(‘.’,’NvarChar(Max)’) As Item
From @XML.nodes(‘t’) as records(r)

Reply Cancel

Bhavin Katrodiya

November 16, 2017 at 1:57 pm

SELECT
num
FROM (
SELECT
num,
SUM(num) OVER(ORDER BY %%PhysLoc%% ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Col2
FROM
tbl_ids
) Data
WHERE Col2>0

Reply Cancel