Android Sqlite order by desc not working correctly
Solution 1
i want result: 2.1, 6.6, 7.5..... so on
That's an ascending order, not descending. Ascending is the default but you can also specify ORDER BY column ASC
.
Id: 8 ,Score: 10.1 ,Letter: S
Id: 6 ,Score: 11.1 ,Letter: S
Id: 7 ,Score: 2.1 ,Letter: S
Id: 3 ,Score: 6.6 ,Letter: S
Id: 2 ,Score: 7.5 ,Letter: S
Id: 4 ,Score: 8.8 ,Letter: S
Id: 1 ,Score: 9.4 ,Letter: S
Id: 5 ,Score: 9.7 ,Letter: S
This is an ascending order but sorted lexicographically (alphabetically), not numerically. If you want numerical sorting, either set the gpoint
column type to REAL
or cast the values to REAL
:
CREATE TABLE ... (... gpoint REAL ...)
or
SELECT ... ORDER BY CAST(gpoint AS REAL)
Solution 2
please change this query:
String selectQuery = "SELECT * FROM " + TABLE_SCORES+" ORDER BY gpoint DESC";
convert gpoint on REAL .
String selectQuery = "SELECT * FROM " + TABLE_SCORES+" ORDER BY cast(gpoint as REAL) DESC";
i hope this is useful.
Munkhdemberel L
Updated on June 30, 2022Comments
-
Munkhdemberel L almost 2 years
I am very new to Android with Sqlite database programming. I want to order by desc my game score column. Game score columns type is real and when i try to order by gpoint and desc not working correctly. Please give me some advice. Code is below:
public List<Score> getAllScores() { List<Score> scoreList = new ArrayList<Score>(); String selectQuery = "SELECT * FROM " + TABLE_SCORES+" ORDER BY gpoint DESC"; SQLiteDatabase db = this.getWritableDatabase(); Cursor cursor = db.rawQuery(selectQuery, null); if (cursor.moveToFirst()) { do { Score score = new Score(); score.setID(Integer.parseInt(cursor.getString(0))); score.setPoint(cursor.getFloat(1)); score.setLetter(cursor.getString(3)); score.setDate(cursor.getString(2)); scoreList.add(0, score); } while (cursor.moveToNext()); } return scoreList; }
my test insertions are:
Calendar c = Calendar.getInstance(); SimpleDateFormat df = new SimpleDateFormat("dd-MMM-yyyy"); String formattedDate = df.format(c.getTime()); Log.d("Insert: ", "Inserting .."); db.addScoreToDB(new Score((float)9.4, "S",formattedDate)); db.addScoreToDB(new Score((float)7.5, "S",formattedDate)); db.addScoreToDB(new Score((float)6.6, "S",formattedDate)); db.addScoreToDB(new Score((float)8.8, "S",formattedDate)); db.addScoreToDB(new Score((float)9.7, "S",formattedDate)); db.addScoreToDB(new Score((float)11.1, "S",formattedDate)); db.addScoreToDB(new Score((float)2.1, "S",formattedDate)); db.addScoreToDB(new Score((float)10.1, "S",formattedDate));
result is:
12-24 15:52:24.065: D/Insert:(9901): Inserting .. 12-24 15:52:24.290: D/Reading:(9901): Reading all contacts..
Id: 8 ,Score: 10.1 ,Letter: S
Id: 6 ,Score: 11.1 ,Letter: S
Id: 7 ,Score: 2.1 ,Letter: S
Id: 3 ,Score: 6.6 ,Letter: S
Id: 2 ,Score: 7.5 ,Letter: S
Id: 4 ,Score: 8.8 ,Letter: S
Id: 1 ,Score: 9.4 ,Letter: S
Id: 5 ,Score: 9.7 ,Letter: Si want result: 2.1, 6.6, 7.5..... so on