On Sep 12, 2011, at 6:51 AM, Igor Tandetnik wrote: > Mr. Puneet Kishor <punk.k...@gmail.com> wrote: >>>> The table geo can also have rows with min_age = max_age. I want a result >>>> set with geo.id, min_age, max_age, age_bottom, age_top, >>>> name, color like so: >>>> >>>> - every row should be for one and only one geo record. I have 39K rows in >>>> "geo" table, so the result set should have 39K rows. >>>> >>>> - when min_age = max_age, list the corresponding intervals.name and color >>>> >>>> - when min_age != max_age, find the interval with the smallest different >>>> between age_bottom and age_top that would span min_age >>>> and max_age of geo. In other words, the interval whose age_bottom is >>>> bigger than the age_bottom of the max_age and whose age_top >>>> is smaller than the age_top of the min_age. > > Something like this: > > select geo.id, min_age, max_age, age_bottom, age_top, name, color > from geo left join intervals i on i.id = ( > select id from intervals > where age_bottom >= > (select age_bottom from intervals where name = geo.max_age) > and age_top <= > (select age_top from intervals where name = geo.min_age) > order by (age_bottom - age_top) limit 1 > ); >
Thanks Igor. The above does work and produces the correct result. The query speed, however, is pretty slow ~ 75 seconds. So, I created indexes on intervals.name, geo.max_age, and geo.min_age, and that brought the query time to ~ 11 seconds. Still too slow. So, I created an intermediate table to hold the results, and the speed is more satisfactory. Many thanks again. Puneet. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
