On 2/4/15, Mark Summerfield <m.n.summerfi...@googlemail.com> wrote:
> Hi,
>
> In the documentation on page http://www.sqlite.org/lang_with.html
> there is an example of a recursive query if you scroll down to the heading
> "Controlling Depth-First Versus Breadth-First Search Of a Tree Using ORDER
> BY".
>
> The second example under that heading shows how to get a depth-first
> search. Here's the query:
>
> WITH RECURSIVE
> under_alice(name,level) AS (
> VALUES('Alice',0)
> UNION ALL
> SELECT org.name, under_alice.level+1
> FROM org JOIN under_alice ON org.boss=under_alice.name
> ORDER BY 2 *DESC*
> )
> SELECT substr('..........',1,level*3) || name FROM under_alice;
>
>
> It turns out that this query not only provides a correctly indented output
> of the tree, but it also sorts every branch alphabetically by name.
>
> What I don't understand is *why* it sorts alphabetically by name.e
The ORDER BY clause within the recursive CTE determines the order of
recursion (depth-first or breadth-first), not the order of final
output.
The final output order, in this case, is the same as the PRIMARY KEY
order on the table. (SQLite does not guarantee this - it just happens
to fall out because of the particular algorithm that SQLite chose in
this particular case. The result might change at any time.)
If you change the table schema to say:
name TEXT PRIMARY KEY DESC,
(Adding "DESC" after PRIMARY KEY) then you will observe that the
output is in the opposite order.
Emphasis: There is ORDER BY clause on the final SELECT statement, and
so SQLite is free to output the results in any order it wants. The
fact that is happens to come out in some particular order in the
current implementation is just a happy accident and is not something
you should rely upon.
>
> I would have expected to need to change the query to have ORDER BY 2 DESC,
> org.name
> for it to work, but it works anyway.
>
> Thanks!
>
--
D. Richard Hipp
d...@sqlite.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
