CODEWITHSUNDEEP
androidsqliteandroid-sqlite
CodingIntrigue
CodingIntrigue

Reputation: 78545

ORDER BY SUM() not working on Android

This seems like a really basic query, which works fine on my desktop, but the results are not ordered when executing on Android:

SELECT column1, SUM(column2) AS column3 
FROM ftdata WHERE ftdata MATCH 'column1:example'
GROUP BY column1
ORDER BY SUM(column2) DESC
LIMIT 8

I've also tried wrapping this in an inner table to no avail:

SELECT * FROM(
SELECT column1, SUM(column2) AS column3 
FROM ftdata WHERE ftdata MATCH 'column1:example'
GROUP BY column1) X
ORDER BY X.column3 DESC
LIMIT 8

This is the code I'm using to run the query in case it helps:

Cursor c = mDb.rawQuery(sql, null);
if(c != null && c.moveToFirst()) {
    do {
        results.add(c.getString(0));
    }
    while(c.moveToNext());
}
c.close();

I should also clarify that ftdata is a virtual table used for FTS querying. Its definition is as follows:

CREATE VIRTUAL TABLE ftdata USING fts3(
column1,
column2 int)

Upvotes: 0

Views: 201

Answers (1)

Simon Dorociak
Simon Dorociak

Reputation: 33505

Try to use this:

SELECT column1, SUM(column2) AS my_sum 
FROM ftdata WHERE ftdata MATCH 'column1:example'
GROUP BY column1
ORDER BY my_sum DESC
LIMIT 8

Upvotes: 2

Related Questions