Selecting a row for a given amount

[watsmyname]

Hello,

 

Suppose i have a table structure

 

mID          pointtoIncrease              amountFrom            amountTo

1                    0                                      0                            99

2                    1                                      100                          499

3                    3                                      500                          999

4                    5                                      1000                        1499

 

I have to select "pointoIncrease" for given amount. Like i have amount 550, then the point is 3 because 550 lies between amountFrom=500 and amountTo=999. Sounds simple but i m not being able to write a query for that, can anyone please help me.

 

Thanks

 

[PFMaBiSmAd]

SELECT pointtoIncrease FROM your_table WHERE amount BETWEEN amountFrom AND amountTo

[watsmyname]

SELECT pointtoIncrease FROM your_table WHERE amount BETWEEN amountFrom AND amountTo

 

Thanks for the reply, i think query is not working. The query is

SELECT pointtoIncrease FROM sa_mileagepoints WHERE '1000' BETWEEN amountFrom AND amountTo

 

So, it should result 5 only, but the query is returning 3 rows, 1,5 and 0

 

and if amount is 550, it returns two rows 3 and 0

 

[PFMaBiSmAd]

What is the data type of your amountFrom  and  amountTo columns?

 

It would need to be an INT for math comparisons to work. Also, by putting the '1000' inside of single-quotes in the query, that makes it a string and strings are compared character by character starting with the left-most character.

[watsmyname]

What is the data type of your amountFrom  and  amountTo columns?

 

It would need to be an INT for math comparisons to work. Also, by putting the '1000' inside of single-quotes in the query, that makes it a string and strings are compared character by character starting with the left-most character.

Oh thanks, got it the single quotes was causing a problem, thanks a lot