Room DB is changing order of items when execute get

Question

There is absolutely standard case I save the items in my Room DB

There are 4 items. After I saved it with help of Stetho I see that them all were saved in right order 1, 2, 3, 4 like it should be.

Then when I need to get them all

@Query("SELECT * FROM mytable WHERE name = :i AND state = :iS")
List<MyObj> getAll(String i,  String iS);

I get order like this 1, 2, 4, 3

Why? What am I doing wrong?

Answer 1

There is no guarantee that your data will come back in the same order. In SQL, order is not an inherent property of a set of data. And Room is an abstraction on top of SQLite (a SQL db engine). You need to order the data as suggested.

Answer 2

you need to specify how the ordering should be, it's either you use the primary key/index key e.g if your table entity template is like this

@Entity
class Mytable {

   @PrimaryKey(autoGenerate = true)
   var id: Int? = null
   ...
}

you can use this to retrieve and order according as it has been inserted

'SELECT * FROM mytable WHERE name = :i AND state = :iS ORDER BY id ASC'

or

'SELECT * FROM mytable WHERE name = :i AND state = :iS ORDER BY id DESC'

and from what i see in your code, the reason you have that is the way you query your data, you can shear with me what you plan to archive and some part of your code.