iip <iip.umar.ri...@gmail.com> wrote: > I tried to create Cartesian list from 8 digit text that has 256 row. when I > try below statement, the time to fetch the result was very long, I'm always > press ctrl+c because its run more than 20 minute: > > select a.a,b.b,c.c,d.d, max(length(a.a||b.b||c.c||d.d)) as e from (select > text8) as a from dict) as a cross join (select text8) as b from dict) as b > cross join (select text8) as c from dict) as c cross join (select text8) as > d from dict) as d WHERE a.a<>b.b and a.a<>c.c and a.a<>d.d and b.b<>c.c and > b.b<>d.d and c.c<>d.d;
First, this can't be the actual query you are running - it has way more closing parentheses than it has opening ones. Second, this must be the most inefficient way ever of picking four longest strings. You do realize that you have SQLite manufacture, and then go through, 4 billion rows, right? See if this works for you: select distinct text8, length(text8) from dict order by length(text8) desc limit 4; > So, above select only use 1 column from the same table, with total row are > 256 row. I know that the value of max will be 8*4, I just want to know the > speed to get maximum number from column calculation using cartesian list. You can't fight combinatorial explosion with brute force. It always wins. > Is there a better/fast way to get data combination of column text8, or I > have to store it physically into table? Again, you are contemplating a table of 4 billion records. What do you think you need it for? What's the ultimate goal of the exercise? -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users