database maximum of 10 and sort according to score, and delete the replacement of the score if it is the lowest

Question

Hi guys im creating game and im new in sql lite. i have a code for inserting database but i dont know how to maximum it at 10 and also i dont know how to compare the new score of the player to the older scores. also how can i delete in the top 10 scored the lowest if the new score beat 1 of the top 10 score. im looking for great codes with good explanation so that i will understand the flow of codes.

Heres my database class:

        public class highscore {

 private static final String DATABASE_NAME = "topscoredb";
 private static final String DATABASE_TABLE = "tablescore";
 private static final int DATABASE_VERSION = 2;
 public static final String KEY_ROWID = "_id";
 public static final String KEY_SCORE = "score";
 public static final String KEY_NAME = "players_name";

private DbHelper ourHelper;
private final Context ourContext;
private SQLiteDatabase ourDatabase;


private static class DbHelper extends SQLiteOpenHelper{



        public DbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // TODO Auto-generated constructor stub
    }

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub

        db.execSQL("CREATE TABLE " + DATABASE_TABLE + " (" +
                     KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                     KEY_NAME + " TEXT NOT  NULL, " +
                     KEY_SCORE + " TEXT NOT NULL);"

                );

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion,
                int newVersion) {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXIST" + DATABASE_TABLE);
            onCreate(db);

        }
 }



   public highscore(Context c){
  ourContext = c;
  }

 public highscore open() throws SQLException{
 ourHelper = new DbHelper(ourContext);
 ourDatabase = ourHelper.getWritableDatabase();


  return this;

 }

  public void close(){
   ourHelper.close();

    }



   public long createEntry(String name, String tscore) {
// TODO Auto-generated method stub

  ContentValues cv = new ContentValues();
  cv.put(KEY_NAME, name);
  cv.put(KEY_SCORE, tscore);
 return  ourDatabase.insert(DATABASE_TABLE, null, cv);
}

 public String getData() {
String[] columns = new String[]{ KEY_ROWID, KEY_NAME, KEY_SCORE};
Cursor c = ourDatabase.query(DATABASE_TABLE, columns, null, null, null,null, null);
String result = "";

int irow = c.getColumnIndex(KEY_ROWID);
int iname = c.getColumnIndex(KEY_NAME);
int iscore = c.getColumnIndex(KEY_SCORE);

for(c.moveToFirst(); !c.isAfterLast(); c.moveToNext()){
    result = result + c.getString(irow)+ " " +c.getString(iname)+" "+ 
c.getString(iscore) + "\n";
     }

return result;
    }

    }

should i use method?

A similar kind of answer is already on stackoverflow.http://stackoverflow.com/questions/2035670/limit-an-sqlite-tables-maximum-number-of-rows?rq=1 — Gaurav Vashisth, Feb 05 '13 at 04:37

score 1 · Answer 1 · edited May 23 '17 at 12:04

1

well android uses sqlite and you can use the solution in Limit an sqlite Table's Maximum Number of Rows.

You can also get the results in descending order

ourDatabase.query(DATABASE_TABLE, columns, null, null, null,null,KEYSCORE + " DESC");

and show top 10 results.

or use SQlite query builder method to limit the results http://developer.android.com/reference/android/database/sqlite/SQLiteQueryBuilder.html#query(android.database.sqlite.SQLiteDatabase, java.lang.String[], java.lang.String, java.lang.String[], java.lang.String, java.lang.String, java.lang.String, java.lang.String)

edited May 23 '17 at 12:04

Community

1
1

answered Feb 05 '13 at 05:31

Gaurav Vashisth

7,547
8
35
56

ourDatabase.query(DATABASE_TABLE, columns, null, null, null,null,KEYSCORE + " DESC"); is this for database that has no data? it was epic fail to my code.. i know that the desc is for descending but how about the name of the player. theres something wrong in the code that you suggest – Manny Dimalanta Feb 06 '13 at 15:34

score -1 · Answer 2 · answered Feb 05 '13 at 04:36

-1

we can store the all the scores in Database and write a write a query get the first ten highest scores...

select * from ( select empno , sal , rank() over (order by sal desc) as rnk from emp where rnk <= 5);

answered Feb 05 '13 at 04:36

srinivas

163
9

can you further explain to me. post some example. – Manny Dimalanta Feb 05 '13 at 04:45
simple thing we have only one table..insert the every thing means all scores but while showing time get the only ten top records. – srinivas Feb 05 '13 at 05:02
This does not work in SQLite. – CL. Feb 05 '13 at 07:59

database maximum of 10 and sort according to score, and delete the replacement of the score if it is the lowest

2 Answers2