Anyway to change row number in SQLITE rawquery?

Question

I've got the following cursor set up to fill a dialog box with a users payment history

Cursor PaymentsCursor = db.getReadableDatabase().rawQuery(
                "SELECT _id, Date, Payment FROM tblPaymentHistory WHERE DebtName = '"
                        + debtname + "'" + "ORDER BY _id ASC", null);
        SimpleCursorAdapter HistoryAdapter = new SimpleCursorAdapter(this,
                R.layout.paymenthistoryrow, PaymentsCursor, from, to);

The problem is though that if there is more than one type of debt, and payments are made to each debt out of order, when the payment history returns it's results, it returns as out-of-order row numbers, for example 1,2,6,7,9,12,etc. I know it's pulling the _id (unique key) from the database, but is there a way to re-base or change the row number in the query, so that each result returns as "1,2,3,4,5,etc" regardless of original ID?

I thought that the ORDER BY _id or even ORDER BY Date ASC would fix this but it didn't.

My rows in the database look something like this:

1, TEST, 4/13/2012, 250
2, TEST, 4/13/2012, 300
3, TEST, 4/14/2012, 222
4, TEST2, 4/14/2012, 500
5, TEST, 4/15/2012, 600

When the user clicks history for "TEST", it returns back as 1,2,3,5... and if they pull up history for "TEST2", it shows as "4", I'm trying to get it so TEST shows "1,2,3,4" and TEST2 shows "1"

Damn I can't answer my own answer, but here's what I ended up doing:

Thanks guys. I found an alternate option that modified the view, so as not having to touch the SqLite db. heres the link that i foundModifying SimpleCursorAdapter's data

And here is the result:

PaymentsCursor = db.getReadableDatabase().rawQuery(
            " SELECT _id, Date, Payment FROM tblPaymentHistory WHERE DebtName = '"
                    + debtname + "'" + "ORDER BY _id ASC", null);

    String[] from = new String[] { DbAdapter.KEY_HISTORY_ID,
            DbAdapter.HISTORY_DATE, DbAdapter.HISTORY_PAYMENT };

    int[] to = new int[] { R.id.PAYMENTNO, R.id.PAYMENTDATE,
            R.id.PAYMENTAMOUNT };

    SimpleCursorAdapter HistoryAdapter = new SimpleCursorAdapter(this,
            R.layout.paymenthistoryrow, PaymentsCursor, from, to);

    HistoryAdapter.setViewBinder(new SimpleCursorAdapter.ViewBinder() {
        @Override
        public boolean setViewValue(View view, Cursor cursor, int column) {
            if (column == 0) { // let's suppose that the column 0 is the
                                // date

                TextView tv = (TextView) view;
                String rownum = String.valueOf(cursor.getPosition() + 1);
                // here you use SimpleDateFormat to bla blah blah
                tv.setText(rownum);

                return true;
            }
            return false;
        }
    });

    paymenthistory.setAdapter(HistoryAdapter);

It may not be the most glamourous way, but now each time the window comes up with the history, it's using the row number (plus one) to indicate which # it is. Thanks all!

why don't you take the number you get from [Cursor#getPosition()](http://developer.android.com/reference/android/database/Cursor.html#getPosition%28%29) — zapl, Apr 13 '12 at 21:18
NEVER concatenate values with a raw query string and execute it. http://en.wikipedia.org/wiki/SQL_injection — Austyn Mahoney, Apr 13 '12 at 21:41
@AustynMahoney Are you saying to change it to something like: PaymentsCursor = db.getReadableDatabase().rawQuery(" SELECT _id, Date, Payment FROM tblPaymentHistory WHERE DebtName = ?"+ "ORDER BY _id ASC", new String[] { debtname }); dang I can't get this to format as code correctly. Sorry — Evan R., Apr 13 '12 at 23:04

score 1 · Accepted Answer · 2012-04-13T21:45:38.653

Here is one way to get the "re-based" ids. In this example, the "new ids" are based on the grade (i.e. the "old ids" in your case):

.headers on

create table foo (name text, grade int);

insert into foo values ('Joe', 45);
insert into foo values ('Anna', 98);
insert into foo values ('Julie', 78);

select name, 
       grade, 
       (select count(*) from foo t1 where t1.grade>=t2.grade) as rank  
from foo t2;

select name, 
       grade, 
       (select count(*) from foo t1 where t1.grade>=t2.grade) as rank  
from foo t2
order by rank;

Having saved this as foo.sql, I get this:

[someone@somewhere tmp]$ sqlite3 < foo.sql 
name|grade|rank
Joe|45|3
Anna|98|1
Julie|78|2
name|grade|rank
Anna|98|1
Julie|78|2
Joe|45|3

score 0 · Answer 2 · answered Apr 13 '12 at 22:18

I've played a bit with what @sixfeetsix answered and since that fails to give 1,2,3,4,.. numbering in combination with the WHERE you might need to put in more subqueries (maybe not but, I'm not that good with queries):

SELECT ( 
           SELECT count( * ) + 1
             FROM  ( 
                   SELECT *
                     FROM tblPaymentHistory 
                    WHERE DebtName = ? 
               ) 
               AS t1
            WHERE t1._id < t2._id 
       ) 
       AS _id,
       Date,
       Payment
  FROM tblPaymentHistory AS t2
 WHERE DebtName = ?
 ORDER BY _id;

put in java String and leave the ? in there to get escaped values (injection safe):

Cursor PaymentsCursor = db.getReadableDatabase().rawQuery(
                "...WHERE DebtName=? .. WHERE DebtName=? .. ", new String[]{ debtname, debtname }); 
        SimpleCursorAdapter HistoryAdapter = new SimpleCursorAdapter(this,
                R.layout.paymenthistoryrow, PaymentsCursor, from, to);

score 0 · Answer 3 · answered May 15 '12 at 04:26

This query worked for me after long research...

Empirical results I derived :

1)You need to define where condition in sub-query also. 2)if ids (t2._id <= t1._id) compared in relation operator will be primary keys then it will work fine in all cases.
3)Regarding orderby condition you have to decide that according to your choice or need.

SELECT 
(SELECT COUNT(*) FROM table t2 WHERE t2._id <= t1._id AND t2.Recipe_id = 2) AS RowNumber,_id,Recipe_id,col2,col3,col4
FROM table t1
WHERE Recipe_id = 2
ORDER BY _id

How it works:-

Say we have a sequence of primary keys 1,2,3,4,5,6 in some table t

Now we create two aliases of it using table t1 and table t2

Now both have same sequence table t1 -> 11,12,13,14,15,16
                            table t2 -> 11,12,13,14,15,16

now this condition ( WHERE t2._id <= t1._id ) compares first primary key "11" of t2 with the first primary key "11" of t2 as 11=11 it will return count() that only one row exists, hence we get "1" in row number..

*** remember for every row in Outer query the sub-query is executed ***

Hence now outer query is at row second having primary key "12"

now it will again compare the ( WHERE t2._id <= t1._id ) this time again t2._id contains "11" while t1._id contains "12"..

Quiet clear it will return that TWO rows are there which are having ids <= 12 that is 11 and 12

this way it will generate the desired sequence.........

This is a simple trick to generate the sequence.. not simple actually in one look but really simple when you get into depth of it..

I am not expert but this is what i understood.. Hope the explanation helps...

As there are various solutions or same solutions available on net but no explanation..

:)

Anyway to change row number in SQLITE rawquery?

3 Answers3