VLOOKUP style range lookup in T-SQL

Question

Here is a difficult problem that I could not figure out. I am using SQL Server 2008, and I have a rare range table that looks like this:

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

Then I have another table with values ​​in these ranges. I would like to build a query that combines these two tables and gives me a 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 want my connection to return:

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

(because 29> 23 for construction, but <= 54 for medicine)

Is there any way I can force SQL to bend at my will this way, except that it actually blows the range table to include all possible values?

Thanks.

Answer 1

You can 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 does not need a maximum range.

Answer 2

An easy way to do this is to add another column to your rare range table.

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

Then use this query to get the range (table 2 corresponds to the value 29,1,60):

 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; 

Answer 3

I think I understand your problem. I created a table called professions with your values ​​and a map_vals table with search 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 setting 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 can also use CROSS APPLY, as shown by manfred-sorg , but this requires 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 the 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 required sorting makes it less efficient than using MAX .