Consecutive Integers

[SMerrill]

I know some of you are dead-set against cursors, so please see if you can help me with this one. (It is not homework.)

I have a very large table (730,000 rows). I need to determine if an integer in one field of the composite key in that table is consecutive for 10 rows, 100 rows, or 1000 rows. If it is consecutive for that many rows, I need to pick the topmost row value and display it in one row along with the denomination.
The values in this one column are always 5 digits long.

Example input:

30022
30030
30031
30032
30033
30034
30035
30036
30037
30038
30039
30040
30043
30049
30074

Example output:

30030, 10

Please note that 30040 will not be in the output, because it is not consecutive from 30040-30049 inclusive.

Other outputs might say:

34300, 100

or

30030, 10
34300, 100
42000, 1000

Thanks for your help,
--Shaun

 

[tlbroadbent]

Interesting exercise. What might the purpose of this be?

Does the following meet your need? Make sure the table has an index on the integer value column.
[tt]
SELECT
t.Int_val,
(Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+9) As Cnt
FROM Tbl_name AS t
WHERE Int_val % 10=0
AND (Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+9)=10

Union All

SELECT
t.Int_val,
(Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+99) As Cnt
FROM Tbl_name AS t
WHERE Int_val % 100=0
AND (Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+99)=100

Union All

SELECT
t.Int_val,
(Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+999) As Cnt
FROM Tbl_name AS t
WHERE Int_val % 1000=0
AND (Select count(*) From Tbl_name
where Int_val between t.Int_val and t.Int_val+999)=1000;

Order By Int_val[/tt] Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[SMerrill]

Thank you for your inspiration, Terry.

This comes from the routing table from a PBX, or telephone switch. Some of our PBX's have a maximum of 10,000 routes, and are nearly maxed out. So if we can combine the consecutive telephone numbers which go to the same route into blocks of 10 or 100, we can route them as a block, thus freeing up 9 or 99 routes for other usage.

Here is the code that worked, edited to make the most sense as an answer to the posted question. As you can see, your answer was nearly right on.

SELECT Int_Val, Block FROM
  (SELECT Int_Val, 
    (SELECT COUNT(*) FROM Tbl_Name x
     WHERE x.Int_Val BETWEEN B.Int_Val AND B.Int_Val + 9
    ) AS Block
    FROM Tbl_Name B
  ) A
WHERE Block = 10 AND Int_Val % 10 = 0

UNION ALL

SELECT Int_Val, Block FROM
  (SELECT Int_Val, 
    (SELECT COUNT(*) FROM Tbl_Name x
     WHERE x.Int_Val BETWEEN B.Int_Val AND B.Int_Val + 99
    ) AS Block
    FROM Tbl_Name B
  ) A
WHERE Block = 100 AND Int_Val % 100 = 0

UNION ALL

SELECT Int_Val, Block FROM
  (SELECT Int_Val, 
    (SELECT COUNT(*) FROM Tbl_Name x
     WHERE x.Int_Val BETWEEN B.Int_Val AND B.Int_Val + 999
    ) AS Block
    FROM Tbl_Name B
  ) A
WHERE Block = 1000 AND Int_Val % 1000 = 0