On Wednesday, 1 May, 2019 15:56, Tom Bassel <tbas...@pettymail.com> wrote:
>In this page in the docs: >https://sqlite.org/queryplanner.html#searching >it says: >"The rows are logically stored in order of increasing rowid" >Would this imply that executing a SELECT would always return the rows >in order or increasing rowid? Mayhaps yes, mayhaps no. However, a relational database does not return "rows". It returns a "result set" which is all the rows simultaneously. That they seem to appear one at a time is simply an implementation limitation imposed by the rather limited thought and computational abilities of humans and their primitive technology. At the present time, your query will be solved by visiting the candidate rows and returning the rows one at a time (side by each) as each candidate is visited and that candidate is decided to be a member of the "result set". >So that a "SELECT * from MyTable" would return all the rows in ROWID >order because that is how they are stored. No. If you want the "result set" presented in a specific order (that is if you depend on the ordering), then you must specify the order you wish them returned. Otherwise, the database is free to return the individual rows comprising the result set in any order it pleases. In the simple case of SELECT * FROM TABLE it is likely that the order will be by rowid, however, if you are selecting a subset of the columns and there are indexes, then the most efficient visitation order may be a covering index and not by an in-order table scan. SELECT * FROM MyTable ORDER BY ROWID; will return the rows of the result set in order by ROWID (assuming that you do not have a column named ROWID that is not the INTEGER PRIMARY KEY). The ordering that you have specified will be taken into account when deciding how to solve your query and the result set will be sorted into that order for row-by-each presentation if necessary (or the sort step my be entirely skipped if the most efficient visitation order happens to visit the candidates in the order you requested -- this decision is made by the query planner). --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
