freshgrill
asked on
MSSQL get max number in range for multiple ranges
I have a list of non-continuous numbers in a column on table 1, on table 2 of categories that show number sequences.
example
table 2
1 returns 1xxxx
2 lost 2xxxx
3 found 25xxx
table 1
14008
15001
25003
I want to find out the next number for each group.
Long way:
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?
example
table 2
1 returns 1xxxx
2 lost 2xxxx
3 found 25xxx
table 1
14008
15001
25003
I want to find out the next number for each group.
Long way:
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?
ASKER
@ewangoya: Table 1 just shows the number sequence, ie. any number starting with 1..... is returns, basically 10000-20000.
Use
with CTE (ID, Numbers)
as
(
select
case
when number > 10000 and number < 20000 then
1
when number > 20000 and number < 25000 then
2
else
3
end as ID,
number as Numbers
)
select ID, Max(Numbers) +1
from CTE
group by ID
Oops I missed the table
with CTE (ID, Numbers)
as
(
select
case
when number > 10000 and number < 20000 then
1
when number > 20000 and number < 25000 then
2
else
3
end as ID,
number as Numbers
from Table2
)
select ID, Max(Numbers) +1
from CTE
group by IDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select id, max(number) + 1
from table2
group by id