CODEWITHSUNDEEP
androidsqliteandroid-sqlite
cfrz
cfrz

Reputation: 325

How do I query my database with WHERE clause and use ORDER BY DESC for the last 5 most recent records

The current query I have gives me all the Treatment objects of a certain Room, using roomId. I need to return only the last 5. Can I use the db.query method? Or will I have to write a rawQuery SQL statement?

Should I just add ORDER BY DESC and snip my list array?

Which would be better?

public List<Treatment> getLastFiveTreatmentsOfRoom(long roomId) {
    // var to store last 5 treatments
    List<Treatment> listTreatments = new ArrayList<Treatment>();
    Cursor cursor = mDatabase.query(DatabaseOpenHelper.TABLE_TREATMENT, mAllColumns, DatabaseOpenHelper.KEY_TREATMENT_ROOM_ID
    + " =?", new String[] { String.valueOf(roomId) }, null, null, null);
    if (cursor!= null) {
        cursor.moveToFirst();
    }
    while (!cursor.isAfterLast()) {
        Treatment treatment = cursorToTreatment(cursor);
        listTreatments.add(treatment);
        cursor.moveToNext();
    }
    // make sure to close cursor
    cursor.close();
    return listTreatments;
}

These are my fields - "added" is the field I need to sort by: its TEXT NOT NULL 

public static final String TABLE_TREATMENT = "treatment";
public static final String KEY_TREATMENT_ROWID = "_id";
public static final String KEY_TREATMENT_ISOLATION = KEY_ISO;
public static final String KEY_TREATMENT_TREATEDBY = "treated_by";
public static final String KEY_TREATMENT_ADDED = "added";
public static final String KEY_TREATMENT_HOSPITAL_ID = "hospitalId";
public static final String KEY_TREATMENT_ROOM_ID = "roomId";

this is the code to fill the added field

Calendar calendar = Calendar.getInstance();
java.util.Date now = calendar.getTime();
java.sql.Timestamp currentTimestamp = new java.sql.Timestamp(now.getTime());
String addedTime = currentTimestamp.toString();

Upvotes: 1

Views: 354

Answers (1)

Arlind Hajredinaj
Arlind Hajredinaj

Reputation: 8519

Here is the query method signature:

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

the before last parameter is the sql ORDER BY and the selection parameter is the sql WHERE.

So what you need is to change the following line:

Cursor cursor = mDatabase.query(DatabaseOpenHelper.TABLE_TREATMENT, mAllColumns, DatabaseOpenHelper.KEY_TREATMENT_ROOM_ID
    + " =?", new String[] { String.valueOf(roomId) }, null, "YOUR_COLUMN DESC", null);

Upvotes: 1

Related Questions