Or this, which is even simpler:
select t1.key,
max(t2.rev) as maxrev,
t2.data
from t1
left join t2
on t1.key == t2.key
and rev < :rev
group by t1.key
order by t1.key;
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>Behalf Of Keith Medcalf
>Sent: Monday, 30 September, 2019 19:31
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>Subject: Re: [sqlite] Lookup join
>
>
>You mean something like this:
>
> select key,
> maxrev,
> data
> from (
> select key,
> null as maxrev,
> null as data
> from t1
> where key not in (select key
> from t2
> where rev < :rev)
> union all
> select t1.key,
> max(rev) as maxrev,
> data
> from t1, t2
> where t1.key == t2.key
> and rev < :rev
> group by t2.key
> )
>order by key;
>
>t1 should have an index on key
>t2 should have an index on key, rev
>
>--
>The fact that there's a Highway to Hell but only a Stairway to Heaven
>says a lot about anticipated traffic volume.
>
>>-----Original Message-----
>>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On
>>Behalf Of Fredrik Larsen
>>Sent: Monday, 30 September, 2019 04:12
>>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>>Subject: [sqlite] Lookup join
>>
>>Consider query below;
>>
>>SELECT key
>>FROM t1
>>LEFT JOIN (
>> SELECT key,max(rev),data
>> FROM t2
>> WHERE rev < ?
>> GROUP BY key
>>) USING (key)
>>ORDER BY key ?
>>LIMIT ?
>>
>>In above query sqlite will materialize the t2-sub-query and then start
>>working on the outer query. I have a lot of data in t2 so this will
>>consume
>>a lot of time.
>>
>>To overcome this I perform above query manually i two stages; I fetch
>the
>>t1 data, then for each row I do a lookup and manually join. This is very
>>fast by but makes it hard to reuse this base-query in other queries.
>>
>>So my question is; Can sqlite do lookup-type joins, like to do manually
>>in
>>code, to avoid the overhead of materializing the full t2-query on all
>>keys,
>>and using just a fraction of this work?
>>
>>I suspect the answer is no, if so, maybe this is solvable through a
>>custom
>>virtual table? I have looked at ext/misc/eval.c, and this custom
>function
>>could be used if a function where alloed to return multiple columns..
>>
>>Fredrik
>>_______________________________________________
>>sqlite-users mailing list
>>sqlite-users@mailinglists.sqlite.org
>>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
>
>_______________________________________________
>sqlite-users mailing list
>sqlite-users@mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
