Post reply

Get count of last consecutive rows with 0 value

  • November 23, 2015 at 5:35 am

    #302449

    I have following data

    create table TestTable (id smallint identity (1,1), Score smallint )

    GO

    INSERT INTO TestTable ( Score ) VALUES

    ( 0), ( 6 ), ( 5 ), ( 4 ), (0 ), (0 ), ( 0 ), ( 3 ), ( 0 ), (0 ), (0 ),(0);

    select * from TestTable order by id desc;

    -- drop table TestTable

    I am required to pick number of last consecutive 0 values. As in this case there are four consecutive zeros at end of table. Please suggest optimize query for it.

    DBDigger Microsoft Data Platform Consultancy.

  • November 23, 2015 at 6:28 am

    #1841680

    because this looks a bit like homework, let me throw out suggestions and not the solution.

    the key here is that you need to test consecutive values. to do that, you need to join the table against itself, so you can compare id=12 to id = 11, but in a set based fashion.

    start with modifying your last query to join your TestTable against itself, and consider how you need to offset the join condition to compare previous rows.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 23, 2015 at 6:45 am

    #1841691

    I wouldn't actually use a join.

    Instead just use a condition to get values after the last non zero value. In order to do that, you need to figure how to get the last non zero value, and then use it to validate against the values in your table.

    For good practices, you need to do this in a "single statement" to avoid problems of concurrency.

    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 3 posts - 1 through 2 (of 2 total)

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