CODEWITHSUNDEEP
androidsqliteandroid-sqlite
AndreaF
AndreaF

Reputation: 12385

Cannot sort value correctly in SQLite

I have a table in this form

Name|Quantity|
Item1|600.2|
Item2|8.12|
Item3|78|
Item4|9.723|

where all Quantity values are text,

I want to sort all items in descendent order so I have tried this

Cursor c = mDb.rawQuery("SELECT *, CAST(myTableName.Quantity as FLOAT) as Quantity FROM myTableName ORDER BY Quantity desc", null);

But the result is this

Item4|9.723|
Item2|8.12|
Item3|78|
Item1|600.2|

Apparently in the query the dot is ignored in the sorting.

How could I fix this issue getting the right descendant sorting?

Upvotes: 0

Views: 498

Answers (1)

Sam
Sam

Reputation: 86948

You can either change the schema to set Quantity to an REAL or use a cast in the ORDER BY clause:

SELECT * FROM myTableName ORDER BY CAST(Quantity AS REAL) DESC;

(In SQLite FLOAT is the same as REAL, you can use either.)

Upvotes: 3

Related Questions