CODEWITHSUNDEEP
mysql
jasne
jasne

Reputation: 53

How to order by nearest float value

My MySQL table:

id | NUMER
1  | 1.233
2  | 4.233
3  | 5.123
4  | 1.146

float value is 5.4554

and I need to select rows from above table with order by nearest NUMBER to my float value

Example output:

5.123
4.233
1.233 
1.146

SQL FIDDLE

Upvotes: 0

Views: 631

Answers (3)

user2464660
user2464660

Reputation: 

select *
from Table1
order by SUBSTRING(NUMER,
          Locate(NUMER ,'.', 1)+3,
                          length(NUMER))

SQL FIDDLE

AFTER 31 Attempts I finally got the answer.

Upvotes: 2

Brad Christie
Brad Christie

Reputation: 101604

SET @floatValue := 5.4554;
SELECT   id, NUMER
FROM     Table
ORDER BY ABS(@floatValue - NUMER)

Just order by the difference between the two. 5.4554-5.123 is ~0.3, where as 5.4554-1.146 is ~4. (Although i think your demo may be off, shouldn't 1.233 come before 1.146?)

Anyways, example.

screenshot of query

Upvotes: 1

user359040
user359040

Reputation:

Try:

order by abs(numer - float)

Upvotes: 4

Related Questions