1

I have a table that contains prices for specific metalic parts with differents sizes. In order to find the right price I need to find the field where the diameter fits in the range of sizes.

It is possible that the range does not exist because of a first time order special sizes or not produced parts like very small pieces -> 1-9.

For example :

A part that has a diameter D of 37 should find the price P designated with X (because 37 is in the range of 35-49).

D(mm) :  10  | 20  |  30  |  35  |  50  |  60 |

P($)  :  45  | 46  |  70  |   X  |  89  | 100 |

How can I achieve that in my Delphi code using SQL request to a Firebird database?

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
VirussInside
  • 187
  • 17
  • 1
    So, leaving aside v.small parts, it sounds like for a given diameter D, you are looking for the range whose lower bound is the maximum diameter which is less than or equal to D and whose upper bound is the minimum diameter which is equal to or higher than D. Do you agree? If so, it should be straightforward to translate that into SQL. – MartynA Feb 24 '20 at 09:59
  • If I understand your suggestion right it is in fact pretty easy. I just need to find the first lower/equal diameter. But I NEED to make sure that the system doesn't find something too small by just searching a diameter that is lower. Can I simply select the first diameter that is lower and ordering DESC ? – VirussInside Feb 24 '20 at 10:13
  • 1
    Well, there are now answers which are far more concise than what I was thinking of and pretty much what you were suggesting. Obviously, in Delphi, you would parameterize the query with the diameter. – MartynA Feb 24 '20 at 11:57

2 Answers2

3

You can select the one row using filtering and limiting to one row:

select first 1 t.*
from t
where t.d <= 37
order by t.d desc;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
3

To select the first diameter smaller than or equal to the wanted diameter

select price
from component_price
where diameter <= 37
order by diameter desc
fetch first row only

Note, fetch first row only was introduced in Firebird 3. If you're using an earlier version, you will need to use FIRST or ROWS instead.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197