SQLite issues with float type and order

Question

I´m working with an iPhone app which has a SQLite database set up. I have a field in the database which is FLOAT type, so I have values like 10,500.00. The thing here is that if I select that table and order by that field, values are ordered as if it was a string value.

Here is an example of what I´m getting:

SELECT floatField FROM table ORDER BY floatField ASC;

And the result is:

109,800.00
48,950.00
53,600.00
54,790.00
74,305.00

This is obviously wrong, cause 109,800.00 > 48,950.00 as float values. So I think SQLite is working with this values as if they were strings, where 109,800.00 < 48,950 where string '1' < string '4'.

If I cast the value to float using this query:

SELECT cast(floatValue as Float) FROM table ORDER BY floatField ASC;

The result I get is this:

109
48
53
54
74

So this is also wrong.

Any ideas will be welcome!

Answer 1

I suggest not to use the comma when inserting the data. SQLite transforms the data a bit, so that

insert into test values(1234.5);
insert into test values('1234.50');
insert into test values('1,234.5');

are all different. Use the first version if possible, only that way you are sure the value is parsed correctly.

Myself, I can't reproduce the problem, but maybe because I don't use the same version of SQLite.