SQLite Forum

ORDER BY not working for a specific DB/table
Very interesting! However, I’m confused about your “exactly rounded” scenario, aren’t there always base-10 rational numbers that can’t be specified exactly as a base-2 floating point number?

I’d like to find some documented consensus on how to treat currency in binary. Fixed point arithmetic just doesn’t seem right to me, and I’d like to think that even a mere 64 bit floating point number has enough precision to represent all the money in the world. It seems to me that one should convert currency to binary once, and then all arithmetic to be done in binary, only converting it back to currency when showing results.

However, that approach can cause rounding to the nearest significant digit to happen one way or another, depending on which way you do a mathematically identical calculation. What is the best approach? When you calculate e.g. pairs that sum up to a fixed total, make sure to calculate the other half of the pair using the rounded first half?