SQL joining on >=

Question

I have a table like this in ORACLE

a     b
--   --
1000   1
100    2
10     3
1      4

My other table has numbers like '67' or '112' in a column called numbers for example. How can I join to this table using those values and get the correct result where >=1000 would be 1 and >= 100 would be 2 >=10 would be 3 etc.

I tried to do a

select g.b
from table o
join table2 g on o.column >= g.a

when I do this say 1002 was the value of g I would get the back these results. 1 2 3 4

when I just want 1

Answer 1

Here's one way to do it using a subquery to get the MAX of column a, and then rejoining on the same table to get b:

select t.numericvalue, t2.b
from (
  select t.numericvalue, max(t2.a) maxb
  from table1 t
    join table2 t2 on t.numericvalue >= t2.a
  group by numericvalue
  ) t join table2 t2 on t.maxb = t2.a

SQL Fiddle Demo

Answer 2

Easiest would be if your lookup table had ranges instead of just one number, such as row 1 = 1000,9999,1 and row 2 = 100,999,2 etc. Then your join might be

SELECT OtherTable.n, lookup.b
from OtherTable
LEFT JOIN lookup on OtherTable.n between lookup.low and lookup.high

But, if you really want to use your original table, then on SQL Server, do this:

/*CREATE TABLE #A (a int,b int)
INSERT INTO #A VALUES (1000,1),(100,2),(10,3),(1,4)

CREATE TABLE #B (n INT)
INSERT INTO #B VALUES (67),(112),(4),(2001)
*/
SELECT B.n,  A1.b
FROM #B B OUTER APPLY (SELECT TOP 1 a,b FROM #A A WHERE A.a<B.n ORDER BY A.a DESC) A1