mysql get value within a range from table

Question

+------------+---------+
|    Column1 |  Column2|
+------------+---------+
|     25     |    5    |
|     30     |    5    |
|     35     |    5    |
|     40     |   5.5   |
|     45     |   5.5   |
|    100     |    6    |
+------------+---------+

let say i have table like above.

i want to get Column2 value based on number within a range of Column1.

example : if input 24 or less, return 5 if my inputs are 30 or 32, should return me 5 if my input are 57, return me 5.5 if input 110, return 6

what sql should i run to get the value?

@od3n can you pls tell whats the column1 range to specify the value for column 2 — zxc
– zxc, Commented Aug 2, 2013 at 10:48
@bew less than 25, 25 to 29.99, 30 to 39.99, 40 to 44.99, 45 to 99.99, 100 or more — od3n
– od3n, Commented Aug 2, 2013 at 11:59

juergen d · Accepted Answer · 2013-08-02 10:47:00Z

4

This will give the the closest result. Not quite sure if this is what you want

select column2
from your_table
order by abs($input - column1)
limit 1

answered Aug 2, 2013 at 10:47

juergen d

205k40 gold badges304 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

od3n Over a year ago

i not sure why i cant get correct value with these values inside the table. 25 | 4.3, 30 | 4.5, 35 | 4.7, 40 | 4.7, 45 | 5.3, 50 | 5.5, 55 | 5.7, 100, 6. my input is 34. it return 4.7. the correct value is 4.5

Vahid Hallaji · Accepted Answer · 2013-08-02 11:40:21Z

1

A solution:

SELECT 
    Column2 
FROM 
    TableName 
WHERE 
    Column1 >= $number ORDER BY Column1 ASC LIMIT 0,1

answered Aug 2, 2013 at 11:40

Vahid Hallaji

7,5576 gold badges46 silver badges52 bronze badges

Collectives™ on Stack Overflow

mysql get value within a range from table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related