Get the latest tech news
SQLite: Outlandish Recursive Query Examples
Common Table Expressions or CTEs act like temporary views that exist only for the duration of a single SQL statement. There are two kinds of common table expressions: "ordinary" and "recursive".
Ordinary common table expressions are helpful for making queries easier to understand by factoring subqueries out of the main SQL statement. All common table expressions (ordinary and recursive) are created by prepending a WITH clause in front of a SELECT, INSERT, DELETE, or UPDATE statement. The "ORDER BY checkin.mtime DESC" term in the recursive-select makes the query run much faster by preventing it from following branches that merge checkins from long ago.
Or read this on Hacker News