CODEWITHSUNDEEP
sql-server
Graham Russon
Graham Russon

Reputation: 45

SQL - Find number in a range of numbers

SQL is not my forte, and I am not sure how to ask the question, some help would be much appreciated.

I need to extract a record that falls between a range of numbers. So I have a number e.g 230, that would return a rate of 60, based on the table below. 

MinR    MaxR   Rate
1       3000    60.00
3001    5000    50.00
5001    7000    48.00
7001    10000   45.00
10000   999999  43.00

Logically I have tried MinR >=237 and MaxR <=237, to no avail.

Is there a simple statement to achieve this, or should I be tackling this more programatically (cursor, If..then, etc)

Many thanks Graham

Upvotes: 1

Views: 119

Answers (2)

Chaos Legion
Chaos Legion

Reputation: 2970

You can use BETWEEN as follows:

SELECT Rate
FROM YourTable
WHERE 230 BETWEEN MinR AND MaxR - 1

Used -1 part so that you do not get two records for one input.

Upvotes: 1

Heinzi
Heinzi

Reputation: 172220

You're almost there, you just have your logic backwards. Let's look at MinR >=237 and MaxR <=237 and plug in the numbers from the first row:

1 >= 237 AND 3000 <= 237

Is that condition satisfied? Obviously not: 1 is not greater or equal than 237. It works if you do it the other way around:

MinR <= 237 AND MaxR >= 237

or, to improve readability (and to avoid this kind of mistake in the future):

237 BETWEEN MinR And MaxR

Upvotes: 1

Related Questions