CODEWITHSUNDEEP
mysql
Jason Chen
Jason Chen

Reputation: 2577

MySQL selecting range between data

So I have my columns listed as 10, 20, 30, 40, 50 and would like mySQL to select and round up numbers between two selected ranges. So for queries BETWEEN 11 AND 32 I would like it to include the data between 10 and 40.

As is, simply using BETWEEN 11 AND 32 will only get me the values listed between 20-30. Rounding to the nearest 10 also does not alleviate this problem.

Upvotes: 0

Views: 484

Answers (2)

luksch
luksch

Reputation: 11712

try

BETWEEN (FLOOR(lower/10)*10) AND (CEIL(upper/10)*10)

with lower and upper to be integers like 11 and 32, which will result in 

(FLOOR(11/10)*10) => (FLOOR(1.1)*10) => 1*10 => 10
(CEIL(32/10)*10) => (CEIL(3.2)*10) => 4*10 => 40

Upvotes: 1

Marc B
Marc B

Reputation: 360922

x BETWEEN y AND z is the functional equivalent of (y <= x) AND (x <= z). if you want records "outside" of the specified range, then you have to widen your range, or modify the out-of-range values to be IN range.

Upvotes: 0

Related Questions