How to Order My SQLite Database in Descending Order, for an Android App

How do I order my SQLITE database in descending order, for an android app?

Query has two syntax, the syntax you are using, last column represents orderBy, you just need to specify on what column you want to do orderBy +"ASC" (or) orderBy +"DESC"

Cursor c = scoreDb.query(DATABASE_TABLE, rank, null, null, null, null, yourColumn+" DESC"); 

Refer this documentation to understand more about query() method.

How to show the list of sqlite by order in android

  public Cursor query(String table, String[] columns, String selection,
            String[] selectionArgs, String groupBy, String having,
            String orderBy) 

hope you can understand by names. so you need to pass sort order for last parameter.

Since you have spinner with sort selection. just pass the selected sort coulmn name to getinformation method.just past below code on Oncreate Method

sortSpinner = (Spinner)findViewById(R.id.sortSpinner);
        String[] sortItems = new String[]{"Sort by","Course name","Year","Semester", "Grade", "Points"};
        sortAdapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item, sortItems);
        sortSpinner.setAdapter(sortAdapter);
        listView = (ListView) findViewById(R.id.list_view);
        listDataAdaptar = new ListDataAdaptar(getApplicationContext(),R.layout.row_layout);
        listView.setAdapter(listDataAdaptar);
        registerForContextMenu(listView);
        courseDbHelper = new CourseDbHelper(getApplicationContext());

        sqLiteDatabase = courseDbHelper.getReadableDatabase();
        cursor = courseDbHelper.getInformation(sqLiteDatabase,UserCourse.NewCourseInfo.COURSE)
     sortSpinner.setOnItemSelectedListener(new AdapterView.OnItemSelectedListener() {
                @Override
                public void onItemSelected(AdapterView<?> parent, View view, int position, long id) {

                     if(position==0){
                         cursor = courseDbHelper.getInformation(sqLiteDatabase,UserCourse.NewCourseInfo.COURSE);

                     }else if(position==1){
                         cursor = courseDbHelper.getInformation(sqLiteDatabase,UserCourse.NewCourseInfo.YEAR);

                     }}

                @Override
                public void onNothingSelected(AdapterView<?> parent) {

                }
                });

Just pass SelectedSortOrder to getInformation method of your db helper class.

    public Cursor getInformation(SQLiteDatabase db, String SelectedSortOrder){
            Cursor cursor;

            String[] projections = {UserCourse.NewCourseInfo.YEAR,UserCourse.NewCourseInfo.SEMESTER,
                    UserCourse.NewCourseInfo.COURSE, UserCourse.NewCourseInfo.POINTS,UserCourse.NewCourseInfo.GRADE};
 if(SelectedSortOrder.equals(UserCourse.NewCourseInfo.COURSE)){
    cursor=  db.query(UserCourse.NewCourseInfo.TABLE_NAME,projections,null,null,null,null, UserCourse.NewCourseInfo.COURSE + " ASEC");
                return cursor;
            }

    else if(SelectedSortOrder.equals(UserCourse.NewCourseInfo.GRADE)){
cursor=     db.query(UserCourse.NewCourseInfo.TABLE_NAME,projections,null,null,null,null, UserCourse.NewCourseInfo.GRADE + " ASEC");
                return cursor;
    } elseif(AnotherTYPE){  }

or via raw query also you can perfom sort on database. You can replace sortorder by ASEC or DESC

String orderBy ="select COLUMN_NAME from TABLE_NAME orderby ASEC ";
                 Cursor C=database_ob.rawQuery(orderBy, null);

Edited: His new problem with Grade Scores

 public String CREATE_QUERY = "CREATE TABLE " + UserCourse.NewCourseInfo.TABLE_NAME + "("+ UserCourse.NewCourseInfo.YEAR+" TEXT,"+ UserCourse.NewCourseInfo.SEMESTER+" TEXT,"+ UserCourse.NewCourseInfo.COURSE+" TEXT,"+ UserCourse.NewCourseInfo.POINTS+" TEXT,"+ UserCourse.NewCourseInfo.GRADE+" TEXT);";

You are storing GRADE as TEXT .you need to use ORDERBY

ORDER BY UserCourse.NewCourseInfo.GRADE DESC

cursor = db.query(UserCourse.NewCourseInfo.TABLE_NAME, projections, null, null, null, null, "ORDER BY "+UserCourse.NewCourseInfo.GRADE + " DESC");

With integer . you need to save as integer type data. pass as int. you can cast String to int as Integer.parseInt("your value")

 int grade;
 public void setGrade(int grade) {
        this.grade = grade;
    }

android sqlite how to order by time

Please try this...

return db.query(DBHelper.TABLETRANSACTIONS, null, null, null, null, null, C_TAG_SCAN_TIME + " DESC");

Android Sqlite order by desc not working correctly

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)

Need to show a SQLite query in descending order

You need to add an ORDER BY ID DESC to your select statement.

ORDER BY

How to sort data from Database with DESC in Android

if you have a specific column like id then do like

String QUERY_SELECT = "SELECT * FROM " + FavContract.favInfo.TABLE_NAME + " ORDER BY " + column_name + " DESC" ;

How I order by name the database android studio?

Use ORDER BY caluse to order the items

Cursor c = database.rawQuery(
        "Select * From " + PDFDatabaseManager.DATABASE_USERS_TABLE + " ORDER BY " +
                PDFDatabaseManager.KEY_FIRSTNAME + " ASC, " +
                PDFDatabaseManager.KEY_SECONDNAME + " ASC;", null);

N.B: Use ASC for ascending order and DESC for descending order

sqlite sorting by WHERE first

I believe the issue may be due to the semi-colon ; at the end of the IN clause. This would effectively mark the end of the SQL and as such result in the ORDER clause being ignored/omitted.

That is try :-

cursor c = db.query(TABLE_NAME, null, DRILL_TYPE + " IN ('type 1', 'type 2')", null, null, null, SIZE + " ASC", null);

In regard to :-

removing the semicolon at the end of the order part fixed things when
I use IN, but not when I use NOT IN

Using

 cursor c = db.query(TABLE_NAME, null, DRILL_TYPE + " NOT IN ('type 1', 'type 2')", null, null, null, SIZE + " ASC", null);

would sort the rows, that do not have type 1 or type 2, correctly, consider the following (which replicates your situation but with extra data to show the NOT IN results):-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (drill_type TEXT, size INTEGER);
INSERT INTO mytable VALUES
('type 2',4),('type 1',5),('type 2',6),
    ('type 1',1),('type 2',2),('type 1',3),
    ('type 4',14),('type 3',15),('type 4',16),
    ('type 3',11),('type 4',12),('type 3',13)
;

SELECT * FROM mytable WHERE drill_type IN ('type 1','type 2') ORDER BY size ASC;
SELECT * FROM mytable WHERE drill_type NOT IN ('type 1','type 2') ORDER BY size ASC;
SELECT * FROM mytable WHERE drill_type NOT IN ('type 1','type 2'); ORDER BY size ASC; -- will not sort as expected

• Note the above effectively replicates your code

The results would be :-

1. original solution

2. using NOT IN correctly

3. leaving the semi-colon in

How to query SQLite from the last to the first in android?

SELECT * FROM `Tbl_stu` ORDER by ID DESC

public Cursor select_information(){
    Cursor Resultcursor=sql.rawQuery("SELECT * FROM `Tbl_stu` ORDER by ID DESC,null);
    return Resultcursor;
}

---

Related Topics