Hi Vance,
...yes it seems reasonable to expect the same resulting order if your
subsequent (temp creating) query is identical to that which was run
previously. However I can understand, from a theoretical and experience
point of view, why you might feel uneasy about relying on a rowid as the
basis for your operations.
I think the real issue here is 'what happens after new_expression?'. Is it
a requirement that the resulting record set (SELECTed from temp_table) be
in identical order to that which is produced by the initial query which
created temp_table? That will be a problem as there is no guarantee that
will be the case. The concerns regarding ordering then gather significant
weight but perhaps can be addressed with a judicious ORDER BY clause in
'new_expression'. I'm thinking that any complex join operation is very
likely to have an explicit ORDER BY clause or relies on a unique index. If
that ordering is duplicated in 'new_expression' then perhaps better results
can be expected?
You could always grab the resultset from that initial query, plonk in an
appropriate container (list, array, dict etc) in your application language,
and control the rest of the find/next/first/previous/last process from
there. If that's possible or appropriate?
Regards,
Michael.
On Fri, May 24, 2013 at 10:42 PM, <ven...@intouchmi.com> wrote:
> I should have also noted that in most cases the original query is a
> complicated multiple join statement, not a simple table query so there is
> no rowid that I can rely on unless I do generate a temp table.
>
> Vance
>
> on May 24, 2013, ven...@intouchmi.com wrote:
> >
> >Thanks to James, Keith and Michael for your input!
> >
> >I don't have any control over the original query. It may or may not
> include an ORDER
> >BY clause. Ideally it would, which makes the question about repeated
> result order
> >moot.
> >I was hoping that by making the exact same query to build a temporary
> table, SQLite
> >would go through the same procedure thereby loading the temp table in the
> same order
> >that stepping would have generated. If this hope is faulty, it would be
> interesting
> >to know why. And, since the temporary table generates a rowid with
> sequential #s
> >starting with '1', I could use that to control how the various Find
> commands selects
> >a row based on the current value of X. Note that X was originally the
> number of times
> >the original prepared statement was stepped through by repeated calls to
> sqlite_step.
> >Note to Michael - I realize that SQLite does not have find commands which
> is why
> >I have to implement them.
> >I don't think that I can just combine the new criteria with the original
> select because
> >that would most likely cause the rows to be presented in a different
> order.
> >
> >Vance
> >
> >on May 24, 2013, James K. Lowden <jklow...@schemamania.org> wrote:
> >>
> >>On Thu, 23 May 2013 18:38:57 -0600
> >>"Keith Medcalf" <kmedc...@dessus.com> wrote:
> >>
> >>> > And, can I depend on SQLite to generate results in the same order
> >>> > as the original prepare/step sequence and the temp table
> >>> > generation, ie. is the temp table's rowid going to be consistent
> >>> > with the original step order?
> >>>
> >>> If you use an ORDER BY clause, yes. If not, then the rows are
> >>> returned in random order.
> >>
> >>Pardon me while I fix that for you.
> >>
> >> s/random/nondeterministic/
> >>
> >>but for the purposes of discussion much the same: without ORDER BY, the
> >>order cannot be relied on.
> >>
> >>--jkl
> >>_______________________________________________
> >>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
> >
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Michael.j.Falconer.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
