CODEWITHSUNDEEP
sql
user1824871
user1824871

Reputation: 21

Need query to find rows that may have 4, 5 or 6 consecutive numbers

Trying to come up with a query that will find rows that contain 4, 5 or 6 consecutive numbers.

For example: Table MyNumbers contains 6 columns of number combinations from 1 to 52.

Cloumn names are: nbr1 nbr2 nbr3 nbr4 nbr5 nbr6

Row one contains: 1 5 43 50 51 52

Row two contains: 41 42 43 44 45 52 <----- five consecutive numbers

Row three contains: 8 14 38 39 42 50

Row four contains: 1 2 3 4 15 29 <----- four consecutive numbers

Row five contains: 8 14 24 36 48 51

Row six contains: 1 2 3 4 5 6 <----- six consecutive numbers

Need to come up with a query that would find rows 2, 4 and 6 based on containing a result set where there were 4 or more consecutive numbers in that row.

I created a database that contains all possible combinations for a 6 numbers out of 52 (1 to 52). What I would like to do is eliminate rows that have four or more numbers that are consecutive. So I am not sure above would do the trick. For those that asked, I am using sql server 2008 R2.

Upvotes: 2

Views: 676

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107726

Assuming the numbers are always increasing, and not repeating

select *
  from mynumbers
 where nbr4 - nbr1 = 3
    or nbr5 - nbr2 = 3
    or nbr6 - nbr3 = 3

I took the liberty of simplifying it to the fact that for a series of 6 consecutive numbers, there must already be a series of 4 consecutive numbers.

Upvotes: 3

Related Questions