Sorting SQLite table with "ORDER BY" - Android

Question

I have a SQLite db for my highscores table. Currently I am having trouble checking if the new score makes the highscores and also sorting the highscores table.

When the game is over, Results.java is called.

Results.java

total_score = dh.calculateTotalScore(score, percentage);
low_score = dh.check(score, percentage, total_score);
if(total_score > low_score) {
    dh.delete(10);
    dh.insert(score, percentage, total_score);
} else {
    dh.insert(score, percentage, 9999999);
}
dh.sort();

All the methods being called in Results.java are coming from DatabaseHelper.java.

DatabaseHelper.java

public void sort() {
    db.rawQuery("SELECT * FROM " + DB_TABLE + " ORDER BY " + TOTAL_SCORE, null);
}

public long calculateTotalScore(long score, int percentage) {
    long i;
    return i = (percentage * 1000) + score;
}

public long check(long score, int percentage, long sum) {
    Cursor c = db.rawQuery("SELECT " + TOTAL_SCORE + " FROM " + DB_TABLE, null);
    long count = c.getCount();
    long low_score;
    if(count == 10) {
        c.moveToLast();
        low_score = c.getInt(c.getColumnIndex(TOTAL_SCORE));
        return low_score;
    } else {
        return count;
    }
}

public long insert(long score, int percentage, long total_score) {
    ContentValues values = new ContentValues();
    values.put(SCORE, score);
    values.put(PERCENTAGE, percentage);
    values.put(TOTAL_SCORE, total_score);

    return db.insert(DB_TABLE, null, values);
}

public void delete(int row) { 
    db.delete(DB_TABLE, RANK + "=" + row, null);
}

The output for TOTAL_SCORE is being displayed as follows:

1
2
40851
1
2
40804
60811
60811
50816

What I desire is for the output to be in numerical order. Like this:

1
1
2
2
40804
40851
50816
60811
60811

The order they are in above is (I think) just the order they were inserted into the db. No errors happen when the program runs and the program does not crash. More code can be provided if needed.

I don't understand where is the problem? Can you be specific about it. What I means is you want to sort your high score or you want sql returning high score. — Smit
– Smit, Commented Jan 8, 2013 at 17:34
Sorry for not being clear. What I want is for the output to be sorted in numericle order. In otherwords the order needs to be 1,1,2,2,40804,40851,50816,60811,60811. I will update my opening post for more clarity. — Matt
– Matt, Commented Jan 8, 2013 at 17:38
I suggest using a Trigger to have SQLite automatically maintain the ten highest scores or keep all of the scores and use the LIMIT clause. — Sam
– Sam, Commented Jan 8, 2013 at 17:39
Just use order by clause in your sql . If you are concerned how many rows you want you can also use limit clause with your sql. and also if you are concerned about duplicate result then you can use group by — Smit
– Smit, Commented Jan 8, 2013 at 17:40
Triggers do seem daunting at first, but you'll get it. This question addresses something very similar: Rolling rows in SQL table. (The second answer points to the documentation, which might help too.) — Sam
– Sam, Commented Jan 8, 2013 at 18:29

Dariusz · Accepted Answer · 2013-06-10 12:15:42Z

4

SELECT does not modify anything; your sort function has no effect. (The rawQuery function returns the sorted list of records, but you ignore it.)

You have to put the ORDER BY clause into the query that is used to display the scores.

edited Jun 10, 2013 at 12:15

Dariusz

22.5k9 gold badges81 silver badges116 bronze badges

answered Jan 8, 2013 at 17:36

CL.

182k18 gold badges241 silver badges282 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matt Over a year ago

Well I was going about this all wrong a guess. I don't just want to display them in a sorted order, I want to maintain them while they are in storage in a sorted order. I guess my new question is if there is a SQL query that can be run on a db that will return nothing, but sort a table according to a specific column and save the table after the sort. From there I can fetch from it as I want and display.

CL. Over a year ago

Tables in SQL do not have a defined order, and records often get reordered by SELECTs. If you want to have a specific order, you must use ORDER BY in every SELECT.

Collectives™ on Stack Overflow

Sorting SQLite table with "ORDER BY" - Android

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related