CODEWITHSUNDEEP
sqldelphifirebirddelphi-7
VirussInside
VirussInside

Reputation: 187

How to find if a number can be contained in a specific range in Delphi using Firebird DB?

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?

Upvotes: 1

Views: 271

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109014

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269923

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;

Upvotes: 3

Related Questions