Op 1 okt 2015, om 04:10 heeft Philippe Riand wrote: > I have a table with 500,000+ records. The table has a date column, > that I?m using to sort my queries (the columns has an index). Simple > queries on the table work very well, using ORDER BY, LIMIT & OFFSET. > I?m actually extracting ?pages? of rows that I?m displaying in a web > page. Great!. > > Now, instead of a simple select, I?d like to execute a recursive > query using CTE, because the rows are organized in an hierarchy. And > I?m only interested by a page, let's say the first n root records. > Unfortunately, the doc says that the ?initial-select? in a recursive > common table exception may not include ORDER BY, LIMIT or OFFSET. As > a result SQLIte probably scans the whole table, which leads to very > poor performance? With other databases (like PostgreSQL), I don?t > have the problem because they accept ORDER BY, LIMIT and OFFSET on > the initial-select, which limits the scan for the initial-select. > > What would be the proper solution with SQLite? One would involve a > first query that selects the initial row ids, and then pass them as > a condition to the initial-select in the recursive. But does anyone > has a better proposal?
Is the documentation really right? Yes, it is a syntax error to write ORDER BY or LIMIT before UNON. Semantically however there is no limitation. The ORDER BY must be inside a subquery (an inline view): select * from (select * from t order by k) union ... My SQLite version is 3.6.11
