PL/SQL script to find consecutive number


Hi Guys,

This is a script to find a range on number in a table in PL/SQL

select a.*, next2_[column_name] - [column_name] diff2, next_[column_name] - [column_name] diff from (
select [column_name], LEAD ([column_name], 1) OVER (ORDER BY [column_name]) AS next_[column_name], LEAD ([column_name], [range_size]) OVER (ORDER BY [column_name]) AS next2_[column_name] 
from mtnc.table_with_number 
order by [column_name]) a where next_[column_name] - [column_name] = 1 and next2_[column_name] - [column_name] = [range_size]
  • table_name is the name of the table
  • column_name is the name of the column
  • range_size is the size of the range

Hope this ca help