Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 How to query a table and order it based on parent ids
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 10/29/2004 :  08:27:08  Show Profile  Visit AskSQLTeam's Homepage
Bill writes "Windows 2000 Server SP4/SQL Server 2000 SP4

I have a table with:

dataid int
parentid int
dirname varchar(248)

Basically it is a list of directories with associated parent directories. I have been asked to write a query that lists the top directory first, then each of the top directory's direct child directories with each of the child directory's direct child directories under them and so on. Below is sample table information and what the result should look like. The number of child directories may increase. We currently have about 5 levels.

I would appreciate any help/direction you gurus can give me! I am at a complete loss.



-- Sample Table Data

dataid, parentid,dirname

4000, 1000, Contract
5436, 4000, Task1
5695, 4000, Task2
5621, 4000, Task3
5840, 5436, MIS Weekly Report
5928, 5436, CMS Progress Report
6403, 5695, MISS Monthly Report

-- The result set should look like this:

MIS Weekly Report
CMS Progress Report
MISS Monthly Report

Flowing Fount of Yak Knowledge

2878 Posts

Posted - 10/29/2004 :  09:00:31  Show Profile  Visit Seventhnight's Homepage
take a look at this:

Go to Top of Page


United Kingdom
12543 Posts

Posted - 10/29/2004 :  09:23:15  Show Profile  Visit nr's Homepage

Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Aged Yak Warrior

526 Posts

Posted - 10/29/2004 :  09:42:32  Show Profile
Or this (which looks about like Nigel's without the spacing)

set nocount on
declare @level int
set @level = 1

create table #tree (id int identity (1,1), dataid int, level int)

insert #tree (dataid, level)
	select dataid, @level
	from dirtable where parentid = 0 -- or is null

While @@rowcount > 0
	set @level = @level + 1
	insert #tree (dataid, level)
		select d.dataid, @level 
		from dirtable d
		inner join #tree t on t.dataid = d.parentid
		where t.level = @level - 1
		order by dirname

Select dirname from #tree t
inner join dirtable d on = t.dataid
order by id

drop table #tree

I'm curious to know why you want this, because it does not seen very useful.

I want to die in my sleep like my grandfather, not screaming in terror like his passengers.
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000