SQlite Query Sorting by Wrong Column

Question

Here is my table setup:

public static final String TABLE_DEBT = "debt";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_DEBT_NAME = "debt_name";
public static final String COLUMN_DEBT_TOTAL = "debt_total";
public static final String COLUMN_APR = "apr";
public static final String COLUMN_PAYMENT = "payment";
public static final String COLUMN_SPECIAL_RATE = "s_rate";
public static final String COLUMN_SPECIAL_TIME = "s_time";
public static final String COLUMN_FIRST_FEE = "first_apr_fee";
public static final String COLUMN_PAY_DATE = "pay_date";
private static final String DATABASE_NAME = "debt.db";
private static final int DATABASE_VERSION = 1;


private static final String DATABASE_CREATE = "create table " + TABLE_DEBT + "( " + COLUMN_ID + " integer primary key autoincrement, " + COLUMN_DEBT_NAME + " text not null, " + COLUMN_DEBT_TOTAL
            + " text not null, " + COLUMN_APR + " text not null, " + COLUMN_PAYMENT + " text not null, " + COLUMN_SPECIAL_RATE + " text, " + COLUMN_SPECIAL_TIME + " text, " + COLUMN_FIRST_FEE + " text, " + COLUMN_PAY_DATE + " text)";

I am using thse queries in this conditional format to sort by user selection:

    if (DebtPlanner.PlanType.equals("highint")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY apr DESC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    } else if (DebtPlanner.PlanType.equals("lowbal")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY debt_total ASC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    } else if (DebtPlanner.PlanType.equals("highbal")) {
         c = database.rawQuery("SELECT *  FROM debt ORDER BY debt_total DESC;", null);
         Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);
    }

Added Code:

for (c.moveToFirst(); !c.isAfterLast(); c.moveToNext()) {
            indName[j++] = c.getString(c.getColumnIndex("debt_name"));
            indBal[k++] = c.getDouble(c.getColumnIndex("debt_total"));
            indPay[l++] = c.getDouble(c.getColumnIndex("payment"));
            indApr[m++] = c.getDouble(c.getColumnIndex("apr"));
            indSRate[e++] = c.getString(c.getColumnIndex("s_rate"));
            indSTime[f++] = c.getInt(c.getColumnIndex("s_time"));
            indDay[d++] = c.getString(c.getColumnIndex("pay_date"));

            rowCounter[n++] = n;
        }


    for (int i : rowCounter) { 

       nameList.add(indName[r++]); // This is the name of each debt that shows out of order.
       // lots of edited calucaltion here

     }

However, they sort, but not by the expected values. For example, when I say to sort by debt_toal (either ASC or DESC), it sorts by debt_name (it gets the ASC or DESC part correct).

I looked at the actual table in the phone and all the data is in the correct columns. I am not even sure where to go from here?

Extra code:

public class DebtPlanner extends Application {

    public static String PlanType = "highint";


    public static String highIntPlan() {
        return DebtPlanner.PlanType = "highint";
    }

    public static String lowBalPlan() {
        return DebtPlanner.PlanType = "lowbal";
    }

    public static String highBalPlan() {
        return DebtPlanner.PlanType = "highbal";
    }

}

score 1 · Accepted Answer · edited May 23 '17 at 12:27

1

When comparing String in Java you must use equals(), not ==:

if (DebtPlanner.PlanType.equals("default")) {

There are great explanations about this in How do I compare strings in Java?

Also you should use else if on your subsequent queries because they are mutually exclusive thoughts.

It has been sorting correctly all along. The problem is, the column is String. So, technically, 2 is larger than 1000000. Now I just need to figure out how to correct this...

Ok, use CAST to sort the column as an Integer rather than Text:

SELECT * FROM debt ORDER BY CAST(apr AS INTEGER) DESC;

edited May 23 '17 at 12:27

Community

1
1

answered Dec 02 '12 at 18:24

Sam

86,580
20
181
179

You're completely correct and I can't believe I missed that. However, the problem is still occurring so please do not close the thread. – TheLettuceMaster Dec 02 '12 at 18:30
Ok, I took the comment down but that may not stop others... Anyway update your code with the two changes above and then update your question with the results. – Sam Dec 02 '12 at 18:35
Great, how do you use the cursor, in a ListView? Post this code, the XML (if you are using a custom layout), and a screen shot would be nice. – Sam Dec 02 '12 at 18:45
I am thinking this may be where the problem lies. This doesn't actually output to a ListView but to a TableLayout (all programatically, no XML). (I am going to be updating this in the future since a Table isn't very efficient--and outdated?) There is way too much code to output for that. I will see what I can find and get back to you though. – TheLettuceMaster Dec 02 '12 at 19:02
You are right, Tables are not the best way to display large amounts of data. ListViews are the best choice for this because they only display what can fit on the screen and cache the rest. – Sam Dec 02 '12 at 19:14
I figured it out. It has been sorting correctly all along. The problem is, the column is `String`. So, technically, 2 is larger than 1000000. Now I just need to figure out how to correct this... – TheLettuceMaster Dec 03 '12 at 00:01
I knew there was something tricky happening, I updated my answer. – Sam Dec 03 '12 at 00:09

score 1 · Answer 2 · answered Dec 02 '12 at 18:26

1

Use

Log.d("TAG", "DebtPlanner.PlanType is " + DebtPlanner.PlanType);

to ensure that you have correct data in your multiple ifs

answered Dec 02 '12 at 18:26

Danil Onyanov

210
1
4
16

Thanks for the tip. I did that and I am getting the correct results in the `if` according to the log. – TheLettuceMaster Dec 02 '12 at 18:35
1

try change your code structure to short: replace **highint** to **apr** and others. Then try: `c = database.rawQuery("SELECT * FROM debt ORDER BY "+DebtPlanner.PlanType+" DESC;", null);` – Danil Onyanov Dec 02 '12 at 18:43
I like the way to simplify that code. But, I'm not sure why, I get several Window Leaking LogCat's on that. – TheLettuceMaster Dec 02 '12 at 18:55

SQlite Query Sorting by Wrong Column

2 Answers2