1

I am facing an issue while retrieving the top record for same date from the table. Using the latest version of SQLCipher 4.4.2 & room database version is 2.2.6.

id | date_value | product_name | price

  • 1 | 2021/01/20 | ABC | 50
  • 2 | 2021/01/20 | XYZ | 60
  • 3 | 2021/01/20 | XYZ | 65
  • 4 | 2021/01/20 | ABC | 60
  • 5 | 2021/01/21 | ABC | 70

Query:

select t.*, (select t2.id from table_product t2 where t.date_value = t2.date_value order by t2.id desc limit 1) as id1 from table_product t group by t.date_value order by t.date_value

Result:

1 | 2021/01/20 | ABC | 50

returning 1st record, not the latest record of the same date.

Am I doing wrong or is there any other way to get the latest record from the multiple records of the same date?

Please help!!

Thanks

  • Test class

    import net.sqlcipher.database.SQLiteDatabase;

public class AwesomeTest extends SQLCipherTest {

@Override
public boolean execute(SQLiteDatabase database) {

    try {

        // Add your scenario here
        database.execSQL("CREATE TABLE table_product (id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, date_value STRING NOT NULL, date_time_stamp LONG NOT NULL, product_name STRING NOT NULL, price INTEGER NOT NULL)");

        database.execSQL("INSERT INTO table_product (id, date_value, date_time_stamp, product_name, price) VALUES ('1', '2021/02/27', '1614364210000', 'ABC', '10')");

        database.execSQL("INSERT INTO table_product (id, date_value, date_time_stamp, product_name, price) VALUES ('2', '2021/02/27', '1614364211000', 'XYZ', '11')");

        database.execSQL("INSERT INTO table_product (id, date_value, date_time_stamp, product_name, price) VALUES ('3', '2021/02/27', '1614364212000', 'ABC', '13')");

        database.execSQL("INSERT INTO table_product (id, date_value, date_time_stamp, product_name, price) VALUES ('4', '2021/02/28', '1614450600000', 'ABC', '14')");

        String query = "SELECT t.*, (SELECT t2.id from table_product t2 where t.date_value = t2.date_value order by t2.id desc " +
                "limit 1) as id1 from table_product t group by t.date_value order by t.date_value";

        Cursor cursor = database.rawQuery(query, new String[]{});

        if(cursor != null) {
           Log.i("TEST", "Record count is " + cursor.getCount());
           while(cursor.moveToNext()) {
                int index1 = cursor.getColumnIndex("price");
                int price = cursor.getInt(index1);
                Log.i("TEST", "product price is " + price);
           }
           cursor.close();
        }

        return true;
    } catch (Exception e) {
        return false;
    }
}

  @Override
  public String getName() {
      return "Awesome Test";
  }
}
Kishor P.
  • 11
  • 3
  • "Am I doing wrong" -- your date is lacking a time component. "is there any other way to get the latest record from the multiple records of the same date?" -- track the time. I do not know where your `id` value comes from, but I would be surprised if it were **guaranteed** to always be increasing in value. – CommonsWare Mar 02 '21 at 12:21
  • Thanks, @CommonsWare for your comments. The id is auto-increment value. For query testing purposes, when I am creating or opening a database instance without SupportFactory passphrase build. The query returns the right result. I have tested on Database Inspector of Android Studio. While using the SupprtFactory passphrase for creating or opening instance of a database, the above query doesn't work. It returns 1st record, not the latest one. – Kishor P. Mar 02 '21 at 12:52
  • "The id is auto-increment value" -- that can get reset. Do not assume that your auto-generated `id` column is **guaranteed** to be **always** increasing in value. Track the time as well as the date. "the above query doesn't work" -- then provide a sample project that demonstrates the issue. Or, provide a test class for the SQLCipher for Android test suite that demonstrates the issue. [I assume that this post is related](https://discuss.zetetic.net/t/nested-query-not-working/4855), but neither that post nor this SO question has any code beyond a bare SQL statement. – CommonsWare Mar 02 '21 at 13:15
  • Ok, @CommonsWare - I have added a test class code. Yes, your assumption is right. The test result on create table & insert records queries is "OK", but on the last query, it gets "FAILED". – Kishor P. Mar 02 '21 at 15:40
  • Given the structure of your test case, that implies an exception is being thrown. In this case, that is because AFAIK you cannot use `execSQL()` for a `SELECT` statement. You need to use `rawQuery()`, then look at the resulting `Cursor` to see if you are getting what you expect. – CommonsWare Mar 02 '21 at 21:27
  • @CommonsWare, I have changed execSQL() to rawQuery(). Now the result shows "OK" but I am not getting the correct record from the table. It returns 1st record from table for the same date. I want the latest record from table. Expected result ('3', '2021/02/27', '1614364212000', 'ABC', '13') actual result ('1', '2021/02/27', '1614364210000', 'ABC', '10') got from query. – Kishor P. Mar 03 '21 at 08:23
  • I get the same results with ordinary SQLite, running on Android 11. So, your query is at fault, not SQLCipher for Android. – CommonsWare Mar 06 '21 at 00:15

0 Answers0