Prev: Backup and Restore database using T-SQL
Next: Paging in sql 2000(Query Help)
From: Roy Goldhammer on 16 Jun 2010 09:28
Hello there

I have table of directories with Parent_Dir and Dir_ID

I need to create query of tree of my directories

I've used successfully the With cte for this.
But now i need to order them the same way i got it like
0,1
1,2
1,3
2,4
2,7
4,5
7,6

Row_number set it only for each part of query and not the entire racursive.
is there a way do do this?


From: Erland Sommarskog on 16 Jun 2010 18:11
Roy Goldhammer (royg(a)yahoo.com) writes:
> I have table of directories with Parent_Dir and Dir_ID
>
> I need to create query of tree of my directories
>
> I've used successfully the With cte for this.
> But now i need to order them the same way i got it like
> 0,1
> 1,2
> 1,3
> 2,4
> 2,7
> 4,5
> 7,6
>
> Row_number set it only for each part of query and not the entire
> racursive. is there a way do do this?

Since I don't see your query, it's a little difficult to understand
what you are asking for, but can't you just put the call to
row_number in the final SELECT statement, after your CTE?


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: --CELKO-- on 17 Jun 2010 13:06
Consider a Nested Sets model for such hierarchies. Faster, easier,
portable and you get an ordered traversal with the (lft, rgt) pairs.

Think in sets and give up your old procedural mindset; become a real
SQL programmer!
From: Iain Sharp on 18 Jun 2010 05:04
On Thu, 17 Jun 2010 10:06:08 -0700 (PDT), --CELKO--
<jcelko212(a)earthlink.net> wrote:

>Consider a Nested Sets model for such hierarchies. Faster, easier,
>portable and you get an ordered traversal with the (lft, rgt) pairs.
>
>Think in sets and give up your old procedural mindset; become a real
>SQL programmer!

This does not explain how to use the nested sets model to produce the
output in the original input order (which is what the O.P. asked for).
Iain
 | 
Pages: 1
Prev: Backup and Restore database using T-SQL
Next: Paging in sql 2000(Query Help)