SQLite: Order by 'insertOrderinDB'

Question

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);

@LAS_VEGAS android.database.sqlite.SQLiteException: no such column: _ID — A.Quiroga, Sep 29 '11 at 09:10
no , time is duration of a song. I'm sure that i cant use the fields which are in the table , cause they have different meanings that you can think about them , truth me . — A.Quiroga, Sep 29 '11 at 09:26
then i think there is no way. how about new table, can you create? — Caner, Sep 29 '11 at 10:10

score 1 · Answer 1 · answered Sep 29 '11 at 08:29

1

order by _id

should do, given the autoincrement nature of the id.

answered Sep 29 '11 at 08:29

njzk2

38,969
7
69
107

android.database.sqlite.SQLiteException: no such column: _ID: – A.Quiroga Sep 29 '11 at 09:04

score 1 · Accepted Answer · answered Sep 29 '11 at 12:50

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.

score 0 · Answer 3 · edited May 23 '17 at 11:55

0

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'

edited May 23 '17 at 11:55

Community

1
1

answered Sep 29 '11 at 12:35

Tim

5,371
3
32
41

SQLite: Order by 'insertOrderinDB'

3 Answers3

Linked