Thank you! I don't find which sqlite document describes 'between ...
and ...'. Would you please point to me where it is documented?
On Fri, Aug 20, 2010 at 6:06 PM, Jim Morris <jmor...@bearriver.com> wrote:
> If there is an index on (name, position) the a where like below might
> use it.
>
> A1.name=A2.name and A2.position between( A1.position - 10, A1.position + 10 )
>
>
> On 8/20/2010 3:54 PM, Peng Yu wrote:
>> Hi,
>>
>> I have the following code to search for neighboring positions
>> (distance<=10). But it is slow for large data set. I'm wondering what
>> is the most efficient query for such a search. Note that I don't
>> create an index, as I'm not sure what index to create on table A.
>>
>> $ cat main.sql
>> #!/usr/bin/env bash
>>
>> rm -f main.db
>> sqlite3 main.db<<EOF
>>
>> create table A (name text, position integer);
>> insert into A values('a', 1);
>> insert into A values('a', 5);
>> insert into A values('a', 21);
>> insert into A values('b', 3);
>> insert into A values('b', 15);
>> insert into A values('b', 19);
>>
>> .mode column
>> .headers on
>> .echo on
>> select * from A as A1, A as A2 where A1.name=A2.name and
>> abs(A1.position - A2.position)<= 10 and A1.position != A2.position;
>>
>> EOF
>>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
