MSSQL get max number in range for multiple ranges
Posted on 2011-02-17
I have a list of non-continuous numbers in a column on table 1, on table 2 of categories that show number sequences.
1 returns 1xxxx
2 lost 2xxxx
3 found 25xxx
I want to find out the next number for each group.
select * from table 2 as a
left join (select '1' as id, max(number)+ 1 as next_number from table 2 where number > 10000 and number < 20000) as b on a.id = b.id
left join (select '2' as id, max(number)+ 1 as next_number from table 2 where number > 20000 and number < 25000) as c on a.id = c.id
What is a better way of doing this?