SQL select statement similar to vertical search approximate match in Excel

Question

Having a table with columns 'price' and 'quantity'.

e.g:

rec  price   qty
1.   10,00     1
2.    7,50     5
3.    5,00    25
4.    3,00   100

I need to select the price for a quantity of 65. This is the price of record 3. Qty 65 is between qty 25 and 100. How to solve this in a sql query?

Answer 1

You can solve that with an inner SQL statement which tries to find the highest quantity lower than or equal to your requested quantity of 65:

select pce.price
from   prices pce
join   ( select max(qty) qty
         from   prices
         where  qty <= 65
       ) pce2
on     pce.qty = pce2.qty

Here pce2 is the join to match the prices line. The pce table is joined to have access to all fields joined. This will only work correctly if there are no duplicates in prices for qty.