none
sql query to find consecutive number count

    Question

  • hi, 

    i m in search of a query to find count of  consecutive numbers in my table (the table have only one column) which is above a particular value.

    for eg: count of consecutive number >10

    value

    10

    11

    12

    13

    10

    11

    12

    8

    9

     i want the count to be 2

    i want to use only query no other construct like sp... 



    • Edited by ystar Tuesday, June 11, 2013 12:16 PM
    Tuesday, June 11, 2013 12:08 PM

Answers

  • See solution for your case if I understood your needs correctly:

    declare @tab table(Number int, Id int identity(1,1) primary key) insert into @tab (Number) values(10) insert into @tab (Number) values(11) insert into @tab (Number)values(12) insert into @tab (Number) values(13) insert into @tab (Number)values(10) insert into @tab (Number) values(11) insert into @tab (Number)values(12) insert into @tab (Number) values(8) insert into @tab (Number) values(9) ;with cte as (select Number,

    Number - ROW_NUMBER() over (order by Id) as Grp from @tab) select COUNT(DISTINCT Grp) as CountConsecutive from cte WHERE Number > 10



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, June 11, 2013 12:35 PM

All replies

  • Can you show DDL of your table and the column that dictates the order above? I see a few sequences of consecutive numbers in what you showed. Basically, this problem solution is called 'Gaps and Islands' problem. Check this blog post showing one of the solutions:

    Refactoring Ranges


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 11, 2013 12:22 PM
  • i am unable to understand what exactly you need .

    Are you looking for consecutive value greater then 10 i.e 11

    declare @tab table(Number int)

    insert into @tab  values(10)
    insert into @tab  values(11)
    insert into @tab  values(12)
    insert into @tab  values(13)
    insert into @tab  values(10)
    insert into @tab  values(11)
    insert into @tab  values(12)
    insert into @tab  values(8)
    insert into @tab  values(9)




    select COUNT(*) from @tab where Number = 10+1

    Tuesday, June 11, 2013 12:28 PM
  • See solution for your case if I understood your needs correctly:

    declare @tab table(Number int, Id int identity(1,1) primary key) insert into @tab (Number) values(10) insert into @tab (Number) values(11) insert into @tab (Number)values(12) insert into @tab (Number) values(13) insert into @tab (Number)values(10) insert into @tab (Number) values(11) insert into @tab (Number)values(12) insert into @tab (Number) values(8) insert into @tab (Number) values(9) ;with cte as (select Number,

    Number - ROW_NUMBER() over (order by Id) as Grp from @tab) select COUNT(DISTINCT Grp) as CountConsecutive from cte WHERE Number > 10



    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Tuesday, June 11, 2013 12:35 PM
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. What you did post cannot be a table! It cannot have a key.  And since a table has no ordering, you have one run of consecutive numbers from 8 to 13. These concepts are fundamental and you do not know them. The best intro book I know for RBMS is the MANGA GUIDE TO DATABASE; after you have read it, then find a freshman textbook. 




    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, June 11, 2013 12:55 PM
  • If I understand your question correctly

    This is what you're looking for:

    select COUNT(*) from table
    where ColumnName > 10

    Tuesday, June 11, 2013 12:56 PM
  • Rows in a table do not have an order, regardless of how you see them or think of them.  Logically speaking, your query is impossible to implement in a meaningful manner.  I'll also point out that it appears your proposed answer to the query is incorrect - 3 numbers (11, 12, 13) follow the first 10.  What logic do you propose to return a value of 2?
    Tuesday, June 11, 2013 2:59 PM
  • Two series of consecutive numbers more than 10.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Tuesday, June 11, 2013 4:37 PM