1

I have been trying to return my table using db.query in alphabetical order based on the name and save as a List for to populate a spinner.

The db.query does successfully return all the column values, but doesn't return in any order when I try sort.

The column I want to sort by is COLUMN_COMPANY_NAME ("company_name") belong to table TABLE__NAME("companies"). COLUMN_COMPANY_NAME was declared as:

public static final String TABLE_NAME = "companies";
public static final String COLUMN_ID = "company_id";
public static final String COLUMN_COMPANY_NAME = "company_name";
public static final String COLUMN_EMAIL = "company_email";
public static final String COLUMN_PHONE = "company_phone";

private static final String DATABASE_CREATE = "create table " + TABLE_NAME
+ "(" + COLUMN_ID + " integer primary key autoincrement, "
+ COLUMN_COMPANY_NAME + " varchar(45), "
+ COLUMN_EMAIL + " varchar(45), "
+ COLUMN_PHONE + " integer default 0"
+ ");";

private SQLiteDatabase db;

I was wanting to use db.query(table, columns, selection, selectionArgs, groupBy, having order) to get all the columns and rows in the database in a cursor:

protected Cursor Cursor_getAllCompanies(){
    String orderBy = COLUMN_COMPANY_NAME + " Collate NOCASE";
    return db.query(TABLE_NAME, null, null, null, null, null, orderBy);
}//if columns = null, default get all columns

So I would expect the rawQuery to be:

SELECT * FROM companies ORDER BY company_name Collate NOCASE;

I have tried a number of different syntaxes but to no avail. As far as I know the above syntax should return all my columns in (ascending) alphabetical order base on company name, but doesn't.

The question boils down to: How can I get the db.query to return in an alphabetised order? And why isn't standard order command working?

Note: There are a number of similar questions that have been answered before, but none of their solutions currently appear to work for me:

SQLite Query, Nocase Alphabetical

How do I order my SQLite in descending order

SQLite query sorting

Community
  • 1
  • 1
Luke
  • 1,077
  • 13
  • 33
  • It *will* return in order of the returned SQL - as dictated by the ORDER BY clause. I suspect the 'desired order' and the *actual result order* are different. So what is the *actual* order? Show data results. – user2864740 Jun 22 '15 at 01:43
  • @user2864740 - That is effectively the question. Currently it is returning in the order it was added to the SQLite database, but with the "ORDER BY company_name Collate NOCASE" shouldn't the returned order be: alphabetical, case indifferent based on the company_name column of the database? – Luke Jun 22 '15 at 01:47
  • What is the actual value of the `orderBy` string when executed? Basically: SQLite is *not* broken and the adapter is *not* capable of re-ordering results. Therefor the generated SQL is not correct per the expectations. Returning results in 'the order it was added to the SQLite database' sounds odd - eg. either the ordering is not applied or the ordering is on a column like the ID. Case (or no case) the order should be alphabetical, fsvo. – user2864740 Jun 22 '15 at 02:33
  • @user2864740 - Printing the string orderBy (which I create) out to logcat it is "company_name Collate NOCASE" as expected. I have noticed that the database handler is successfully reordering by datetime and int, so am investigating if there is an issue with either my SQLite or compiler... – Luke Jun 22 '15 at 02:46
  • I have just downloaded the same project from git onto another PC in the office, have compiled the code and installed the APP onto the SAME phone. This time the spinner HAS been ordered in correct alphabetical order (based on company name)... Re-programmed with APK from my PC - list is not in alphabetical order. Am reinstalling Android studio to try again... – Luke Jun 22 '15 at 02:54
  • What is the source of the company names? Any chance they are not UTF-8 or UTF-16 and this is a charset problem? – Bob Snyder Jun 22 '15 at 02:58
  • @qbix - The company names are either entered from the APP or received from an API. I do not attempt to filter any UTF-16 coming so it could come through the API potentially. – Luke Jun 22 '15 at 03:00
  • @Luke In that case by best guess is: 'one of the applications was using old code that did not have the order by' (which would explain the behavior) – user2864740 Jun 22 '15 at 03:19
  • @user2864740 - This is probably the most likely reason it was happening. It seems odd that when reprogrammed it did it again though... there might have been an issue with my gradle builds. It all seems to be working now with my reinstalled version of Android Studio. – Luke Jun 22 '15 at 04:19
  • Currently on adroid (expo react native) `columnname COLLATE NOCASE ASC` is working just fine for me – jave.web Mar 09 '22 at 06:13

1 Answers1

0

if your trying to list it in alphabetical order
just SELECT * FROM companies ORDER BY company_name

Agapitz
  • 56
  • 6
  • @Agapitz- this too is not working, in what appears to be the same way. It appears that the ordering is working correctly for ints and datetimes, as I have another database in the same project which sorts by time of a "tickets" creation and the "tickets" receipt number (an int). – Luke Jun 22 '15 at 02:43
  • did you try to run it in sqlitedatabase browser ? – Agapitz Jun 22 '15 at 05:48