We help IT Professionals succeed at work.

How to avoid SELECT automatically sort by primary key in SQLite?

tikusbalap
tikusbalap asked
on
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.
Comment
Watch Question

By the rules of SQL, you can't count on getting records back in any specific order without specifying an ORDER BY clause in your SQL query.

In *practice* servers sometimes return values in the order in which they're inserted, in the order of the first index created, or in the order of the primary key--but you can't count on this behavior, and in fact I've seen the behavior change between database maintenance windows or after the database version is upgraded. You definitely wouldn't want to count on a DB engine to give you back records in any particular order if you write a SELECT statement without an ORDER BY clause.

The only real way to get your records back in the order you inserted them is to create a timestamp column and then sort on it during the SELECT. If you don't want to worry about populating that column on INSERT, have that column auto-populate itself with a timestamp (depending on your DB engine).
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.

Author

Commented:
@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

Author

Commented:
@pcelba
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.