Exclude zero till get first nonzero value.(use cte or select)

  • Hi Friends ,

    input code :

    CREATE TABLE #First_nonzero

    (

    value int

    )

    GO

    INSERT INTO #First_nonzero VALUES

    (0),

    (0),

    (0),

    (0),

    (1),

    (2),

    (0),

    (3),

    (0)

    Expected_output :

    1

    2

    0

    3

    0

    please some one suggest me how can i do this ..?

  • Anandkumar-SQL_Developer (7/21/2016)


    Hi Friends ,

    input code :

    CREATE TABLE #First_nonzero

    (

    value int

    )

    GO

    INSERT INTO #First_nonzero VALUES

    (0),

    (0),

    (0),

    (0),

    (1),

    (2),

    (0),

    (3),

    (0)

    Expected_output :

    1

    2

    0

    3

    0

    please some one suggest me how can i do this ..?

    Regardless of the order in which you have inserted the data, your table's values have no default order.

    So unless you add a column by which to order the data (timestamp? Identity? Sequence?) you cannot do this.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Anandkumar-SQL_Developer (7/21/2016)


    Hi Friends ,

    input code :

    CREATE TABLE #First_nonzero

    (

    value int

    )

    GO

    INSERT INTO #First_nonzero VALUES

    (0),

    (0),

    (0),

    (0),

    (1),

    (2),

    (0),

    (3),

    (0)

    Expected_output :

    1

    2

    0

    3

    0

    please some one suggest me how can i do this ..?

    I think I understand what you're trying to do here but there is problem. At the moment there's no way of ordering the rows. The rows in a table are not stored in any order. The rows could technically be returned in any order so the output may differ each time the query is run. Is there a way that the rows can be ordered?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Here is a modified version.

    IF OBJECT_ID('tempdb..#First_nonzero', 'U') IS NOT NULL

    DROP TABLE #First_nonzero;

    CREATE TABLE #First_nonzero

    (

    Counter INT IDENTITY(1, 1)

    PRIMARY KEY

    ,value INT

    );

    GO

    INSERT INTO #First_nonzero

    (value)

    VALUES (0),

    (0),

    (0),

    (0),

    (1),

    (2),

    (0),

    (3),

    (0);

    SELECT *

    FROM #First_nonzero fn

    WHERE fn.Counter >= (SELECT MIN(fn2.Counter)

    FROM #First_nonzero fn2

    WHERE fn2.value > 0

    )

    ORDER BY fn.Counter;

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • I've assumed that there is a column indicating when row was inserted. I also taken you literally and assumed that non-zero could be a negative rather than just greater than zero. I've changed the 1 to -1 to demonstrate this.

    You can use any ordering column in the WHERE clause so long as there is an ordering column.

    CREATE TABLE #First_nonzero

    (

    InsertDateTime DATETIME

    ,value int

    )

    ;

    INSERT INTO #First_nonzero

    SELECT DATEADD(minute,1,GETDATE()),0 UNION ALL

    SELECT DATEADD(minute,2,GETDATE()),0 UNION ALL

    SELECT DATEADD(minute,3,GETDATE()),0 UNION ALL

    SELECT DATEADD(minute,4,GETDATE()),0 UNION ALL

    SELECT DATEADD(minute,5,GETDATE()),-1 UNION ALL

    SELECT DATEADD(minute,6,GETDATE()),2 UNION ALL

    SELECT DATEADD(minute,7,GETDATE()),0 UNION ALL

    SELECT DATEADD(minute,8,GETDATE()),3 UNION ALL

    SELECT DATEADD(minute,9,GETDATE()),0

    SELECT

    fnz.Value

    FROM #First_nonzero fnz

    WHERE fnz.InsertDateTime >= (

    SELECT MIN(fnz1.InsertDateTime)

    FROM #First_nonzero fnz1

    WHERE fnz1.value <> 0

    )

    DROP TABLE #First_nonzero


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • I also taken you literally and assumed that non-zero can be a negative rather than just greater than zero.

    Good catch, thanks.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Hi BWFC / Phil Parkin ,

    Its working thank u very much for ur valuable time.

  • You're welcome. What did you use to order the rows?


    On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
    —Charles Babbage, Passages from the Life of a Philosopher

    How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

  • Actually This is an interview question.

  • Anandkumar-SQL_Developer (7/21/2016)


    Actually This is an interview question.

    Really? Then unless your answer is along the lines of

    "This cannot be done, unless a column exists on which to order, because rows in tables have no inherent order."

    you should not get the job 🙂

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • Phil Parkin (7/21/2016)


    Anandkumar-SQL_Developer (7/21/2016)


    Actually This is an interview question.

    Really? Then unless your answer is along the lines of

    "This cannot be done, unless a column exists on which to order, because rows in tables have no inherent order."

    you should not get the job 🙂

    If he was asking this during the interview, he shouldn't get the job either, as it would be cheating.

    If it was after the interview, it's good as it shows interest on learning.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Hi this code also work :

    SELECT Value

    FROM

    #first_nonzero

    WHERE %%PhysLoc%% >=

    (

    SELECT MIN(%%PhysLoc%%) FROM #first_nonzero WHERE VALUE > 0

    )

  • Anandkumar-SQL_Developer (7/25/2016)


    Hi this code also work :

    SELECT Value

    FROM

    #first_nonzero

    WHERE %%PhysLoc%% >=

    (

    SELECT MIN(%%PhysLoc%%) FROM #first_nonzero WHERE VALUE > 0

    )

    Not necessarily, the physical location of a row might not have the desired order.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply