Table rows in SQLite are stored in a BTree structure where the "key" is the rowid. As far as I can tell, the SQLite engine does an inorder traversal of the tree when doing a table scan (ie, from lowest to highest) returning rows in rowid order. The rowid is always the primary key of a table. All other keys that are not explicitly named rowid (<name> INTEGER PRIMARY KEY) are merely additional indexes where the key is the concatenation of the key columns plus the rowid of the table row. Thus a table with a non-INTEGER primary key (or a compound primary key declaration) is exactly the same as a unique index on the columns, or just an additional index.
While this is presently how SQLite works, it may not always be so. It is possible that a future version may change the order of traversal. In particular, I believe SQLite4 will not perform tablescan operations in rowid, but rather in storage order. Therefore, at present, if you use the negative timestamp as the key, the traversal order will be from newest record to oldest since the "lowest" rowid will be the most recent timestamp. If you change the step function to update the final return value for each non-null value, you will return the "last" non-null value rather than the "first" -- where first and last means "numerically least" and "numerically greatest" rowid respectively. Of course, this is programming to an internal implementation detail. It may not be transportable to any other database system, and the specific implementation may change at any time. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org > -----Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- > boun...@sqlite.org] On Behalf Of Steinar Midtskogen > Sent: Friday, 19 October, 2012 15:43 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Find first non-NULL values of several columns > > "Keith Medcalf" <kmedc...@dessus.com> writes: > > > Alternatively, to implement your original question, write an aggregate > function which returns the first non-null value it comes across, and use the > negated unix timestamp as an explicit rowid, depending on the fact that a > table-scan does an in-order traversal of the table btree: > > Except for the names, the below code is *exactly*, character by > character, as what I first wrote, but then I realised that the order > of the values going to the step function might not be defined. So can > I trust that I will get the order of my unix time primary key? It > would be convenient if I can omit the timestamp argument in the > function. > > > > > typedef struct fnnCtx fnnCtx; > > struct fnnCtx > > { > > double fnn; > > int flag; > > }; > > > > SQLITE_PRIVATE void fnnStep(sqlite3_context *context, int argc, > sqlite3_value **argv) > > { > > fnnCtx* p = sqlite3_aggregate_context(context, sizeof(fnnCtx)); > > if (p && sqlite3_value_numeric_type(argv[0]) != SQLITE_NULL && p->flag > == 0) > > { > > p->fnn = sqlite3_value_double(argv[0]); > > p->flag = 1; > > } > > } > > > > SQLITE_PRIVATE void fnnFinal(sqlite3_context *context) > > { > > fnnCtx* p = sqlite3_aggregate_context(context, 0); > > if (p && p->flag == 1) > > sqlite3_result_double(context, p->fnn); > > } > > -- > Steinar Midtskogen > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
