Tuesday, April 28, 2009

How to sort the hierarchical recursive query in SQL Server 2005

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

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: