Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 687
  • Last Modified:

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?
0
freshgrill
Asked:
freshgrill
  • 4
1 Solution
 
Ephraim WangoyaCommented:

select id, max(number) + 1
from table2
group by id
0
 
freshgrillAuthor Commented:
@ewangoya: Table 1 just shows the number sequence, ie. any number starting with 1..... is returns, basically 10000-20000.
0
 
Ephraim WangoyaCommented:

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

Open in new window

0
 
Ephraim WangoyaCommented:
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 ID

Open in new window

0
 
Ephraim WangoyaCommented:
Sorry, its from Table1

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 Table1  
)
select ID, Max(Numbers) +1
from CTE
group by ID
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now