Android- SQLite query to Order Results by Day using the query builder

Question

At the moment i'm currently using the query builder to pull all my records from my database and they are currently organised by day however I its sorts in althabetical order,

  public Cursor fetchAll() {

      return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_MODULECODE,
        KEY_DAY, KEY_TIME, KEY_DURATION, KEY_TYPE, KEY_ROOM},null, null, null, null,"Day");
   }

I know after some googling, that I need to replace the final section with a custom search order. something along the lines of :

 select _id, busnum, 
    case cast (strftime('%w', servdate) as integer)
        when 0 then 'Sunday'
        when 1 then 'Monday'
        when 2 then 'Tuesday'
        when 3 then 'Wednesday'
        when 4 then 'Thursday'
         when 5 then 'Friday'
        else 'Saturday' end as servdayofweek
   from tb1
   where ...

   From:
   http://stackoverflow.com/questions/4319302/sqlite-return-as-day-of-week

so far i have been unable to figure out how tocombine the two and anyhelp in the right direction would be much aprechiated.

Edit Soloution thank you for the help I went with declaring the string and linking to it in the query for anyone that needs something simple this is my final code for it

public Cursor fetchAll() {
   String OrderBy ="case Day"+
      " when 'Monday' then 0 "+ 
      " when 'Tuesday' then 1"+
      " when 'Wednesday' then 2"+
      " when 'Thursday' then 3"+
      " when 'Friday' then 5"+
      " end";
 return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_MODULECODE,
        KEY_DAY, KEY_TIME, KEY_DURATION, KEY_TYPE, KEY_ROOM},null, null, null, null,OrderBy );
}

mu is too short · Accepted Answer · 2012-05-02 01:27:00Z

1

If your Day values are the day names then you want something like this:

order by case Day
        when 'Sunday' then 0
        when 'Monday' then 1
        when 'Tuesday' then 2
        ...
        when 'Saturday' then 6
    end

in the SQL; keep in mind that you can hand an SQL ORDER BY pretty much any expression. The orderBy argument to query can be any SQL ORDER BY clause (without the order by) so you want this big ugly string :

"case Day when 'Sunday' then 0 when 'Monday' then 1 ... when 'Saturday' then 6 end"

as the last argument to query. You'll have to properly fill in the ... of course.

answered May 2, 2012 at 1:27

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

Comments

Sam · Accepted Answer · 2012-05-02 01:29:47Z

1

If you cannot figure out how to pass a complex query through SQLiteDatabase.query(... lots of variables...) just pass your select statement as a string with SQLiteDatabase.rawQuery().

For example:

String query = "select _id, busnum, " +
               " case cast cast (strftime('%w', servdate) as integer) " +
               ...
String[] selectionArgs = new String() { yada, yada, yada }
mDb.rawQuery(query, selectionArgs);

edited May 2, 2012 at 1:29

answered May 2, 2012 at 1:24

Sam

87k21 gold badges185 silver badges180 bronze badges

Collectives™ on Stack Overflow

Android- SQLite query to Order Results by Day using the query builder

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related