# Best way to lookup a value from multiple ranges

I need to determine what range a value fits in order to determine the appropriate discount. Just trying to determine the best most efficient way to do this in Access (2016). Here is a simplified table:

My first thought is to loop through a recordset to evaluate each record for >= FROM\$ and =< TO\$ until a range match is found, then grab the discount rate. Would there be a more efficient way to do this with an SQL statement?

We can try using `BETWEEN` here:

``````SELECT
MAX(IIF(price BETWEEN FROM\$ AND TO\$, Discount, NULL)) AS Discount
FROM yourTable;
``````

We might more typically expect that your table of discounts would store information for multiple groups, e.g. products. In that case, we could write:

``````SELECT
product,
MAX(IIF(price BETWEEN FROM\$ AND TO\$, Discount, NULL)) AS Discount
FROM yourTable
GROUP BY
product;
``````

In your example it is impossible to find the discount for a value such as 9.999... it is greater than 9.99 and less than 10 and does not fit inside any of your ranges. I would start by changing your data to:

``````| From | To   | Discount |
|------|------|----------|
| 0    | 10   | 0.05     |
| 10   | 20   | 0.10     |
| 20   | 50   | 0.12     |
| 50   | NULL | 0.13     |
``````

And then you could probe the table like so:

``````SELECT Discount
FROM   Discounts
WHERE  (@Value >= [From])
AND    (@Value <  [To] OR [To] IS NULL)
``````

Assuming you have no gaps in the ranges, I find that in many databases the most efficient method is:

``````select t.*,
(select top 1 d.discount
from discounts d
where t.[\$] <= d.[from\$]  -- the \$ is for whatever column name you actually use
order by d.[from\$] desc
) as discount
from t;
``````

(Note that this ignores `to\$`.)

In particular, this should be able to take advantage of an index on `discounts(from\$, discount)`.