Sorting elements in a database

Question

I have successfully created a database table in android ( SQLite ). I am trying to sort elements in the database according to the first or last name ( ascending /descending ) . ( i have buttons in the context menu for this purpose ) . I am having issues writing the SQL statement . Any idea?

public void onCreate(Bundle savedInstanceState) 
    {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.database);

        ListView lv=(ListView)findViewById(R.id.mylist);


         dbh = new DatabaseHelper(this);
         c = dbh.getReadableDatabase().rawQuery("SELECT _id, " + 
             DatabaseHelper.NAME + 
  ", " + DatabaseHelper.LASTNAME + 
      ", " + DatabaseHelper.ans2 + 
                        " FROM " +
                DatabaseHelper.TABLE_NAME, null); // initializing 


            String[] dataFrom ={DatabaseHelper.NAME, DatabaseHelper.LASTNAME, DatabaseHelper.ans2};
            int[] dataTo = {R.id.name, R.id.value1, R.id.value2};               
            SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, 
                    R.layout.row, c, dataFrom, dataTo);

           lv.setAdapter(adapter);

           registerForContextMenu(lv);


    }

Answer 1

You can do something like this, check this URL http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html

dbh.getReadableDatabase().query(DatabaseHelper.TABLE_NAME,
                                    new String[]{DatabaseHelper.NAME,
                                                DatabaseHelper.LASTNAME, 
                                              ,DatabaseHelper.ans2},null,null,null,
                                             DatabaseHelper.NAME,null);

in the second to last parameter, you set the row which you want to orde by and you can add the string ASC or DESC for example

DatabaseHelper.NAME + " ASC"

Answer 2

I think you are mixing up things. You can't control how it is stored in database. All you need to worry would be how you want to get data from database. You need to use order by clause (By default it is Asc on column you specified).

 c = dbh.getReadableDatabase().rawQuery("SELECT _id, " + 
             DatabaseHelper.NAME + 
  ", " + DatabaseHelper.LASTNAME + 
      ", " + DatabaseHelper.ans2 + 
                        " FROM " +
                DatabaseHelper.TABLE_NAME ORDER BY yourColumnName, null);