How to avoid SELECT automatically sort by primary key in SQLite?
Experts,
My table was like this:
CREATE TABLE t(x INTEGER PRIMARY KEY, y);
then I did insert: INSERT INTO t VALUES (115,200); INSERT INTO t VALUES (16,300); INSERT INTO t VALUES (250,300);
The problem is: My query "SELECT * FROM t" always sorted ascending automatically by x (primary key). I'm using tksqlite gui to test. The result:
16 300
115 200
250 300
How to get records without sorted automatically (based on order of insert) and without additional column e.g: timestamp?
115 200
16 300
250 300
Thank you.
My table was like this:
CREATE TABLE t(x INTEGER PRIMARY KEY, y);
then I did insert: INSERT INTO t VALUES (115,200); INSERT INTO t VALUES (16,300); INSERT INTO t VALUES (250,300);
The problem is: My query "SELECT * FROM t" always sorted ascending automatically by x (primary key). I'm using tksqlite gui to test. The result:
16 300
115 200
250 300
How to get records without sorted automatically (based on order of insert) and without additional column e.g: timestamp?
115 200
16 300
250 300
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Pavel Celba
You may also use some xBase data engine and it will return rows in their physical order if no index or order by is specified.
ASKER
@paulthewolf
Thanks for the explanation. What are you suggest about timestamp column:
1. Primary key based on unix epoch time (since my primary key was generated randomly), or
2. Create additional timestamp column
Thanks for the explanation. What are you suggest about timestamp column:
1. Primary key based on unix epoch time (since my primary key was generated randomly), or
2. Create additional timestamp column
ASKER
@pcelba
I'm sorry. Unfortunately, I can only use SQLite on this project.
I'm sorry. Unfortunately, I can only use SQLite on this project.
@tikusbalap
I wouldn't necessarily make a PK column--two INSERTs at the same time would be a problem (I've seen that happen).
If SQLite can add a column to your table that auto-populates a timestamp value in INSERT, I'd do that - otherwise I'd just bite the bullet and update your INSERT statements to manually add a timestamp that you sort on when doing the SELECTs.
I wouldn't necessarily make a PK column--two INSERTs at the same time would be a problem (I've seen that happen).
If SQLite can add a column to your table that auto-populates a timestamp value in INSERT, I'd do that - otherwise I'd just bite the bullet and update your INSERT statements to manually add a timestamp that you sort on when doing the SELECTs.