Android Room Database Query not working with SQLCipher

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";
    }
  }