SQLite Forum

ORDER BY not working for a specific DB/table
Login
> the nearest representable value of 31.0 is 30.999999999999999999.

This is pretty misleading. 31.0 can be trivially represented with a binary decimal like:

11111.0000000000000000000

ie. 2⁴ + 2³ + 2² + 2¹ + 2⁰

Which has a clear and precise encoding under IEEE754. At double precision, that is 0x403f000000000000.

Of course that is the same encoding that you would get for 
30.999999999999999999, and to understand why I find it instructive to look at how neighbouring bit patterns are interpreted (decimal approximations courtesy of java.lang.Double):

```
0x403effffffffffff = 30.999999999999996
0x403f000000000000 = 31.0
0x403f000000000001 = 31.000000000000004
```

ie. each distinct floating point value can be thought of as representing a region on the line of real numbers, and any real number is indistingiushable from other numbers in the same region.

I'd wager the majority of floating point engines display 0x403f000000000000 as 31.0 so it's surprising that SQLite generates 30.999999999999999999, but it's not incorrect.