VLOOKUP-style range lookup in T-SQL

Here's a tricky problem I haven't quite been able to get my head around. I'm using SQL Server 2008, 开发者_StackOverflow社区and I have a sparse range table that looks like this:

Range     Profession
-----     ----------
0         Office Worker
23        Construction
54        Medical

Then I have another table with values that are within these ranges. I'd like to construct a query which joins these two tables and gives me the Profession value that is less than or equal to the given value. So let's say my other table looks like this:

Value
29
1
60

Then I'd like my join to return:

Value     Profession
-----     ----------
29        Construction
1         Office Worker
60        Medical

(because 29>the 23 for Construction but <=the 54 for Medical)

Is there any way I can get SQL to bend to my will in this manner, short of actually blowing out the range table to include every possible value?

Thank you.

Easist Way to do this is to add a another column to you sparse range table.

LowRange       HighRange      Profession
   0              22         Office Worker
  23              53          Construction
  54            999999         Medical

Then use a query like this to get the range(table 2 is the one with the 29,1,60 values):

SELECT Table_2.JoinKey as Value, Table_1.Description as Profession 
  FROM Table_1 INNER JOIN Table_2 
          ON Table_2.JoinKey => Table_1.LowRangeKey 
             AND Table_2.JoinKey <= Table_1.HighRangeKey;

You could use CROSS APPLY:

select v.Value, p.Profession
from tblValues v
cross apply
   (select top(1) pr.Profession
    from tblProfessionRanges pr
    where pr.Range <= v.Value ORDER BY pr.[Range] DESC) p

It should be faster than using max and doesn't need a max-range do be maintained.

I think I understand your problem. I created a table called professions with your values and a map_vals table with the look up values. Then I came up with this:

select p.range as `range1`, p.profession, v.value from professions p 
inner join map_vals v ON v.value >= p.range
where p.range = 
      (select max(p3.range) from professions p3 where p3.range <= v.value) 
order by v.value

which when given these values...

value   
29
0
60
1
23
54

returns

range1  profession  value   
0           Office Worker   0
0           Office Worker   1
23          Construction    23
23          Construction    29
54          Medical         54
54          Medical         60

EDIT:

You could also use CROSS APPLY as shown by manfred-sorg but it requires an ORDER BY DESC or you will get the following:

select v.Value, p.Profession
from tblValues v
cross apply
   (select top(1) pr.Profession
    from tblProfessionRanges pr
    where pr.Range <= v.Value) p

produces

Value       Profession
----------- --------------------------------------------------
29          Office Worker
1           Office Worker
60          Office Worker

to get your desired result you need to change it to:

select v.Value, p.Profession
from tblValues v
cross apply
   (select top(1) pr.Profession
    from tblProfessionRanges pr
    where pr.Range <= v.Value ORDER BY pr.[Range] DESC) p

Value       Profession
----------- --------------------------------------------------
29          Construction
1           Office Worker
60          Medical

However, the sorting required here makes it less efficient than using MAX.