Numeric Sequence In SQL

Few years ago I came accross an issue, when I needed to find smallest continuous integer in a sequence. My data were like 1, 2, 3, 100, 101, 102, 520, 521, 530 and the task to find lets say next continuous bigger than 100. Long story short, fastest and the final SQL was:

SELECT t1.num+1 FROM table AS t1 
WHERE NOT EXISTS 
   (SELECT t2.num FROM table AS t2  WHERE t1.num+1 = t2.num) 
   AND t1.num>=100
GROUP BY t1.num ORDER BY t1.num LIMIT 1

Today, the issue was a little more difficult. I have to find smallest continuous 2D sequence in any direction.

Read the rest of this entry »