Side question: how much in demand are recursive CTE's? I, particularly, hack recursive functions around all SQL View queries, but it kludges the code up quite a bit. Am I the only one with this problem?
I don't remember anyone asking on the mailing list, but you can search the archives. SQLite does let you do select operations on selects: SELECT ... FROM (SELECT ... FROM ..)
The other important thing SQLite has available is user defined functions. Because SQLite is running in the same process as your query code, you can add your own scalar and aggregate functions that do anything you want.
It also has a virtual table mechanism so you can provide tables of any shape and contents you want based on other data.
A sub-select is not a recursive CTE; think more along the lines of "find me all children of this Hacker News post #" where the scheme is simply "id | parent-id | poster | text": to do this requires recursion. I could totally be misunderstanding the reason you bring them up, however.
