On 18 October 2011 22:09, Fabian <fabianpi...@gmail.com> wrote: > I'm working on a pagination system where two tables need to be joined. . . . > > I got very good advice on this mailing-list, to change the query into this: > > SELECT table1.data1, table2.data2 > FROM > ( > > SELECT table1.data1 > WHERE table1.data1 = 10 > ORDER BY table1.rowid DESC > OFFSET 0 LIMIT 250 > > ) AS table1 > JOIN table2 > ON table1.rowid = table2.rowid . . . > JOIN. So far, so good. > > Then the second problem appeared: the user is free to sort on any column of > the output. So you can have the situation where the WHERE is on table1 but > ORDER BY is on table 2. At first I wanted to solve this by using the old > (slow) JOIN for cases like this. But it turned out to be unworkable: if the > query only returns 3 rows, it was still spending precious seconds sorting > all the other rows in the table. > > Is there any solution for this? All things I tried completely break the > pagination system, because it fully depends on having ORDER and OFFSET in > the same sub-query.
Your queries above produce syntax errors, as there is no rowid from the inner subquery on which to perform the join. Assuming that is a typo, does the following not do what you want? SELECT table1.data1, table2.data2 FROM ( SELECT table1.rowid, table1.data1 WHERE table1.data1 = 10 ORDER BY table1.rowid DESC OFFSET 0 LIMIT 250 ) AS table1 JOIN table2 ON table1.rowid = table2.rowid ORDER BY table2.rowid; Regards, Simon _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
