Post reply

Find Missing Number in a Sequence of numbers

  • May 30, 2010 at 1:37 am

    #221624

    Hi,

    In a table i have a column with name "SortOrder" having following rows

    ID Name SortOrder

    5 aa 5

    6 ab 3

    4 ac 2

    3 ad 6

    7 ae 4

    8 af 7

    In Above Table i need missing number in sortorder column i wrote below query

    select top 1 l.SortOrder +1 as start

    from faqMaster as l

    left outer join faqMaster as r on l.SortOrder+ 1 = r.SortOrder

    where r.SortOrder is null;

    I am getting result if missing number is not "1"

    I mean in above table missing number is 1..but the query is not returning value.

  • May 30, 2010 at 2:19 am

    #1174305

    Why don't you use a Tally (or Numbers) table using a left join, limited to max(SortOrder)? If you don't use a Tally table yet, please have a look at the related link in my signature.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 30, 2010 at 7:39 am

    #1174326

    This was covered in depth in another thread recently.

    Just about every available method is discussed, including using Tally tables, a custom CLR aggregate, and a very fast way to find ranges of missing values (by Jeff Moden).

    See:

    http://www.sqlservercentral.com/Forums/Topic911849-392-2.aspx

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

  • May 31, 2010 at 12:16 am

    #1174399

    Thank you very much, my problem solved

  • June 1, 2010 at 8:27 am

    #1174797

    Good to hear. Thanks.

    Paul White
    All articles available on SQL.kiwi
    @SQL_Kiwi

Viewing 5 posts - 1 through 5 (of 5 total)

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