In SQL Server 2005 was an innovation in the form of CTE (common table expressions), which enables the hierarchical recursive queries to the database. While designing the web site
автокредит на автомобиль I have a challenge: to display the hierarchical structure of pages of the site. The query also have to sort sort the data on a field "pageorder" from the pages with the same level of hierarchy.
The structure of the table looks like this:
CREATE TABLE [dbo].[pages](
[id] [int] NULL,
[pid] [int] NULL,
[title] [nvarchar](max) NULL,
[pageorder] [int] NULL
) ON [PRIMARY]
The field "pid" points to id of the parent record. In addition, there have to exist a root(entry) record, whose pid = NULL.
Below is a sample request that selects and sorts the hierarchical data:
