Post reply

Find missing numbers in sequence 1 thru 5

  • March 25, 2019 at 9:47 am

    #2031683

    Thanks, much appreciated

  • March 25, 2019 at 12:44 pm

    #2031717

    Jeff Moden - Monday, March 25, 2019 7:31 AM

    Jason A. Long - Sunday, March 24, 2019 8:24 PM

    Jeff Moden - Sunday, March 24, 2019 7:22 PM

    Great idea, Jason.  Just a caution though...

    While that will work for the given data, it won't always work... all you need is some gaps in the ID column and it won't work correctly any more.

    However, if you created a computed column as a sequential row number in the same order as the ID column and played the count formula against that instead of the ID column, then that would most likely work all of the time even if there were gaps in the ID column.

    As a bit of a sidebar, if things are supposed to be in complete sets of 5, why is this problem centering around a set of 3?  Seems to me that something went haywire and I'd be loath to just up and delete the evidence.

    Thank you sir. 🙂

    Yes, I did think about that and also the possibility that the Col2 values could be inserted out of order. That's an inherent problem with data designs that that rely on insert order to create meaning. Given the numerous ways in which this type of schema can go south, I figured it was best to simply solve for the data that was supplied and let the OP identify other anomalous data  patters if they exist.

    Good point about researching, rather than deleting... Which reminds me that I should have issued a warning with my previous solution...
    It will delete potentially valid data if it doesn't follow the established pattern. I would suggest executing it as a SELECT, verify that valid data is not going to be deleted, BEFORE actually deleting it.

    Totally agreed on all of that and thank you for taking my comments the right way.  Concerning the bold underlined stuff, let's hope the OP truly understands why the anomalous data is there and that it's not actually an indication of a problem that is getting ready to bite the company.

    I've known you long enough to know that your critiques are of the code and the concepts... never a personal dig. Plus I agree with everything you said. 🙂

Viewing 2 posts - 16 through 16 (of 16 total)

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