On 2018/06/23 2:47 PM, Gert Van Assche wrote:
Barry, that's even easier indeed. And it works perfect! Thanks for sharing this.
I very much like the method which Barry suggested for the simplicity, but have avoided it since I thought it would be significantly slower on a large data set, however, SQLite is known for some pretty neat optimizations (especially where the row_id is concerned) and I'm now interested to know if indeed such optimizations feature in this case. I have a few such implemented queries that might benefit from changing over.
May I ask, if you do have a rather large dataset, and perhaps don't mind the time, would you care to compare the two methods and let us know if there is any discernible difference in speed? Also one small important item - how many F1 items are there typically per unique F2 item in total? Does it grow over time?
Also, you do not need hashes, for either of the two queries - the queries should work regardless of integer or Unicode text used, perhaps the comparison might need a specific collation? Surrounding both references with a TRIM() function might also help. Essentially, if F1 of record 3 in Table T is '*&^%@#*&^@#' then "... FROM T AS A, T AS B WHERE A.F1 = B.F1" must resolve correctly. If it doesn't, it's a bug. The <, <=, >, >= might all produce some Unicode weirdness upon inadequate collations.
If time doesn't allow, then don't spend effort on this, it's simply a curiosity. :)
Thanks! Ryan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
