gaps and islands - SQL interview question -

gaps and islands - SQL interview question -

i got next question on interview: given table of natural numbers missing ones, provide output of 2 tables, origin of number gap in first table , ending in second. example:

____ ________ | | | | | | 1 | | 3 | 3 | | 2 | | 6 | 7 | | 4 | | 10| 12| | 5 | |___|___| | 8 | | 9 | | 13 | |____|

while pretty much same phil sandler's answer, should homecoming 2 separate tables (and think looks cleaner) (it works in sql server, @ least):

declare @temp table (num int) insert @temp values (1),(2),(4),(5),(8),(9),(13) declare @min int, @max int select @min = min(num), @max = max(num) @temp select t.num + 1 range_start @temp t left bring together @temp t2 on t.num + 1 = t2.num t.num < @max , t2.num null select t.num - 1 range_end @temp t left bring together @temp t2 on t.num - 1 = t2.num t.num > @min , t2.num null

sql gaps-and-islands