CODEWITHSUNDEEP
c#sql
kshepherd
kshepherd

Reputation: 13

sql get a number whose range is another number

I have a column of price breaks and need to find the row that contains a certain quantity. It is set up like this:

id | MinimumQuantity | Price
-----------------------------------
1  |        1        |    10
1  |        10       |    20
1  |        25       |    30

...and the quantity could be any number. So, if the quantity is 1 I would need to get the Price 10 (1-10), if the quantity is 15 I would need to get the Price 20 (10-25) and if the quantity is 100 I would need to get Price 30 (25+). So far I have:

select Price from myTable where MinimumQuantity >= @myQuantity and id = @myID

...but of course this doesn't return 25+, it seems like it should be simple but I'm stumped. Thanks for your help.

Upvotes: 1

Views: 51

Answers (1)

Iłya Bursov
Iłya Bursov

Reputation: 24146

here is proper query for mysql:

SELECT `Price` from `myTable`
WHERE @myQuantity >= `MinimumQuantity` and `id` = @myID
ORDER BY `MinimumQuantity` ASC
LIMIT 1

for sql server:

SELECT top 1 [Price] from [myTable]
WHERE @myQuantity >= [MinimumQuantity] and [id] = @myID
ORDER BY [MinimumQuantity] ASC

Upvotes: 2

Related Questions