An index will not remove the requirement for the order by clause, but it will make that origin much much faster. SQL select does not guarantee the order of the records by default, no matter what indexes you have in place. Some engines will default to order off addition, however, that all goes out of the window when rows are removed and or added.
Adding an index and using order by is the correct answer.
This applies to any table no matter how it is created.
No, an index just gives you a faster lookup. If you want a particular order, then use an ORDER BY.
This is made quite clear at SELECT, and see Section 4, in particular the first sentence.
Thank you for correcting my statements, @Ian_Kennedy & @TimStreater
Well, to me this is a warning not to use ChatGPT.
Humm, but ChatGPT did give me the correct answer.
Not about ORDER BY and temp tables, it didn’t.
Not sure how you would deduce that when I’ve not shared chatGPT’s output.
As part of its debugging responses it stated:
1. Ensure
v_duplicatesView is Sorted ProperlyIf
v_duplicatesis a VIEW or TEMP TABLE, SQLite does not guarantee row order unless you explicitly useORDER BY. Try forcing it in your query:xojo
CopyEdit
Var sql As String = "SELECT PK FROM v_duplicates ORDER BY PK, size" Var rows As RowSet = myDatabase.SelectSQL(sql)
SQLite doesn’t maintain order in views unless forced.
Seemed fairly clear to me.
i’ve just pretrained a local model with some Xojo specifics’ by default most LLM will try to use VB when they see xojo Syntax, prompting with xojo will help if they have internet abilities. With a bit of tweaking you can get it pretty close.
You said, upthread:
EG how many would know that when using a temporary SQL table and applying an ORDER BY xxx SQL doesn’t necessarily keep the same order if you do a subsequent SELECT from the same table.
Now you post
which is at best misleading as, in fact, for a SELECT the order is not guaranteed from ANY table without an ORDER BY.
Hence my comment about the value of ChatGPT output.
We must not forget that these models are evolving rapidly and that the skills needed to create working code are a particular focus.
I personally have no doubt that we will soon see more and more projects in which large parts of the code have been created or at least optimized by machines. And if we Xojo developers ignore this, we will once again miss the boat… 
Of Course ! ChatGPYT is v 4.5 since sometimems… 
Tim - I did specifically ask it about Temp tables so its answer is accurate for my purposes. As others have indicated it can be a very powerful tool so should not be dismissed, and it will only get better - I guess “trust but verify” is the right answer.
And it limited its answers to Temp tables, which is not what a good teacher would have done. It was very specific in what it said, viz:
If
v_duplicatesis a VIEW or TEMP TABLE, SQLite …
thereby implying that what it said was limited to only those types of table. Which is misleading.
Thanks Tim
The open sources languages (Python) have an advantage with AI because there is so much code for the AI’s to examine. Developers in those languages have embraced the help of AI code assistants. They are getting better rapidly. IMO this is a very important direction for coding in the future. Code with AI assistance should be enabled as much as possible with Xojo.
Inevitably, but only if it also comes with an “OFF” switch.
Code with AI assistance will definitely accelerate development, but another important consideration is to also have the ability to deploy AI models as part of Xojo solutions (e.g. using something like ONNX).
If Xojo integrate something tied to some external tool, make sure we can turn it off and Xojo still works at the max offline, and also that such experiment is done in such way that it’s very easy for Xojo to remove it (like a plugin) once some “catch” show up from the provider of such service or the markets change to another new shine thing (or China thing).