Recursive Queries with CTE (Common Table Expressions)

Since 3.*.*.1927 db supports recursive CTE queries. Using recursive CTE it is possible to perform SQL queries on hierarchical data. The SQL syntax is identical to SQL Server and there are many articles about it. For example:

http://sqlblog.com/blogs/linchi_shea/archive/2009/04/16/recursive-sql-queries-how-do-they-work.aspx
http://msdn.microsoft.com/en-us/library/ms186243.aspx

Hierarchical relations can be expressed as a recursive single tables' join. For example, consider table:

create table objects
(
id int not null primary key,
name varchar,
parent_id int
);

And insert:

insert into objects(id,name,parent_id) values(1,'Cars',null);
  insert into objects(id,name,parent_id) values(2,'Porsche',1);
    insert into objects(id,name,parent_id) values(3,'911',2);
    insert into objects(id,name,parent_id) values(4,'Boxster',2);
    insert into objects(id,name,parent_id) values(5,'Cayman',2);
    insert into objects(id,name,parent_id) values(6,'Cayenne',2);
    insert into objects(id,name,parent_id) values(7,'Panamera',2);
  insert into objects(id,name,parent_id) values(8,'Aston Martin',1);
    insert into objects(id,name,parent_id) values(9,'DB7',8);
    insert into objects(id,name,parent_id) values(10,'DB9',8);
    insert into objects(id,name,parent_id) values(11,'Vantage',8);
    insert into objects(id,name,parent_id) values(12,'One',8);

Here we have created hierarchy using parent_id field that refers to the parent row: "Cars" is the root (no parent), 2 brands (Porsche and Aston Martin) belongs to "Cars" and each brand has a list of models. Now, define the recursive CTE SQL to read all porsche models:

WITH Cars(id,parent_id,name,path,level) AS
(
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = 2 -- porsche root
   UNION ALL
   --recursive execution
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars;

The result:
id    Parent_id  Name        Path                     Level
----------------------------------------------------------------------------------------
2                1  Porsche     Porsche                      0
3                2  911           Porsche/911               1
4                2  Boxster     Porsche/Boxster          1
5                2  Cayman    Porsche/Cayman         1
6                2  Cayenne   Porsche/Cayenne         1
7                2  Panamera Porsche/Panamera       1

Example using parameterized query and ordering:

Declare @id int
set @id = select id from objects where name = 'porsche';

WITH Cars(id,parent_id,name,path,level) AS
(
   --initialization. read porsche root
   SELECT id,parent_id,name,name,0 as level
   FROM objects
   WHERE id = @id
   UNION ALL
   --recursive execution
   SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1
   FROM cars c
      INNER JOIN objects o ON c.id = o.parent_id
)
select * from cars order by name;