Deutsche Kartoffel Deutsche Kartoffel - 6 months ago 13
SQL Question

SQLite Query a range of entries in interval

I would like to implement a SQL-Query that gives me a range of entries. Example:

public List<Entry> getEntries(int lowerValue, int upperValue){
//Select upper - lower entries.
}

getEntries(0, 20) --> First 20 Entries
getEntries(21, 40) --> Entry 21 to 40
getEntries(12, 200) --> Entry 12 to 200


At the moment I get all entries like this:

public List<Log> getLogs(){
List<Log> list = new ArrayList<>();
SQLiteDatabase db = getWritableDatabase();
String query = "SELECT * FROM " + TABLE_LOGS + " WHERE 1";

//Cursor points to a location in your results
Cursor c = db.rawQuery(query, null);
//Move to the first row in your results
c.moveToFirst();

//Position after the last row means the end of the results
while (!c.isAfterLast()) {
if (c.getString(c.getColumnIndex("type")) != null) {
int id = c.getInt(c.getColumnIndex("_id"));
int type = c.getInt(c.getColumnIndex("type"));
long date = c.getLong(c.getColumnIndex("date"));
int refId = c.getInt(c.getColumnIndex("refId"));
String extra = c.getString(c.getColumnIndex("extra"));

list.add(new Log(id, type, date, refId, extra));
}
c.moveToNext();
}
db.close();
c.close();
return list;
}


I know there is a LIMIT-attribute but that starts always from the first entry.

Answer

What you need is the combination of LIMIT and OFFSET

You could use

LIMIT <skip>, <count>

or

LIMIT <count> OFFSET <skip>

So some examples would be

"SELECT * FROM " + TABLE_LOGS + " WHERE type=1 LIMIT 20 OFFSET 0"
"SELECT * FROM " + TABLE_LOGS + " WHERE type=1 LIMIT 20 OFFSET 20"
"SELECT * FROM " + TABLE_LOGS + " WHERE type=1 LIMIT 20 OFFSET 40"

Hope this helps.

Comments