CODEWITHSUNDEEP
androidsqlite
A.Quiroga
A.Quiroga

Reputation: 5720

SQLite: Order by 'insertOrderinDB'

i need to know how to make a select with the insert order in DB without making any index field by my own . May be this posible?

And , the insertion order in a transaction is linear-mode? (first insert sentences -> first insert )

DB CREATION:

private static final String CREATE_TABLE_SONGS = "CREATE TABLE IF NOT EXISTS SONGS (" +
        "LIST_ID int NOT NULL, " +
        "PLAY_ID int NOT NULL, " + //ID of the song in the list
        "SONG_ID int NOT NULL, " + //ID of the song
        "TITLE varchar(200) NOT NULL," +
        "TIME int NOT NULL, " +
        "PROVIDER int NOT NULL, " +
        "PROVIDER_ID varchar(200) NOT NULL, " +
        "SONG_STATE int NOT NULL, " +
        "PRIMARY KEY (LIST_ID,PLAY_ID));";

And then

db.execSQL(CREATE_TABLE_SONGS);

Upvotes: 0

Views: 955

Answers (3)

Larry Lustig
Larry Lustig

Reputation: 50998

SQLite tables have a "secret" column called ROWID the value of which increases as you add rows. It can serve as a proxy for "order entered into table". ROWID is basically a "free" auto-incrementing integer primary key column for the table.

Note that by definition SQL databases have no notion of row ordering. While I would be surprised if the behavior of ROWID every changed in SQLite (since it's existence and behavior is documented), as a rule if you want to retrieve rows in an ordered fashion in SQL databases you're responsible for storing the ordering information yourself.

Upvotes: 1

Tim
Tim

Reputation: 5421

The best-practices method of sorting rows by the temporal order in which they have been inserted is to use a timestamp column which is assigned as a default value the date-time corresponding to "now", as the row is being inserted. This is easy to do in SQLite.

sqlite database default time value 'now'

Upvotes: 0

njzk2
njzk2

Reputation: 39403

order by _id

should do, given the autoincrement nature of the id.

Upvotes: 1

Related Questions