Solved

ID and parentID - how to sort?

Posted on 2002-06-21
3
433 Views
Last Modified: 2006-11-17
Hi, this is a table I've got (if it doesn't look good, paste it into notepad and use system as font... )

tblProjects
-----------------------------------
|  ID  |   parentID   |  content  |
|----------------------------------
|   1  |     null     |  blabla   |
|   2  |     1        |  blabla   |
|   3  |     1        |  blabla   |
|   4  |     null     |  blabla   |
|   5  |     4        |  blabla   |
|   6  |     4        |  blabla   |
|   7  |     4        |  blabla   |
|   8  |     null     |  blabla   |
|   9  |     1        |  blabla   |
|  10  |     1        |  blabla   |
|  11  |     4        |  blabla   |
|  12  |     4        |  blabla   |
-----------------------------------

These are all different projects and instances of projects. Where ID=1, we have a Mother project. ID 2 and ID 3 are children of that Mother Project. Same is found for Mother project 4 and mother project 8.

I want to send a request to the database whereas it replies with the entire list; listing first the mother project, then the parent projects, under each mother object. The result would be something like this:

-----------------------------------
|  ID  |   parentID   |  content  |
|----------------------------------
|   1  |     null     |  blabla   |
|   2  |     1        |  blabla   |
|   3  |     1        |  blabla   |
|   9  |     1        |  blabla   |
|  10  |     1        |  blabla   |
|   4  |     null     |  blabla   |
|   5  |     4        |  blabla   |
|   6  |     4        |  blabla   |
|   7  |     4        |  blabla   |
|  11  |     4        |  blabla   |
|  12  |     4        |  blabla   |
|   8  |     null     |  blabla   |
-----------------------------------

Get my picture? How can I do this?

Any help appreciated,

Kind regards,
Simon
0
Comment
Question by:NiTech
3 Comments
 
LVL 39

Expert Comment

by:appari
ID: 7098406
select ID, case parentID when null then ID else parentID end, content  from tblProjects
order by 2,1
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 7098407
This is the way to do it:
select SortID = case when parentid is null then id else parentid end, ID, ParentID , Data
from yourtable
order by 1,2

Now, don't be disturbed by the SortID column for output, you should simply ignore it in the Application. If you still want to supress it:

select ID, ParentID, Data from
(select SortID = case when parentid is null then id else parentid end, ID, ParentID , Data
from yourtable) l
select SortID = case when parentid is null then id else parentid end, ID, ParentID , Data
from yourtable
order by SortID, ID

CHeers

0
 

Author Comment

by:NiTech
ID: 7102700
Thank you- this worked excellent.

This is an example of my output:

SortID      ID          ParentID    enDescription                                              
----------- ----------- ----------- ----------------------
195         195         NULL        Autonomy Implementation
226         226         NULL        Additional HelpDeks resource Q1
230         230         NULL        Accenture
235         235         NULL        Alcatel 4980 Implementation
296         296         NULL        Application Support/ Upgrade OOS
348         348         NULL        ATM Workshop
354         354         NULL        Annual leave
354         653         354         Annual Leave Calloff 1
354         654         354         Annual Leave Calloff 1
354         655         354         Annual Leave Calloff 1
361         361         NULL        Application Upgrade - item 001
402         402         NULL        Autonomy

As you can see, it is now very easy to determine whether or not the row is a child or a parent, simply by checking for existance of a ParentID.

Thank you very much.

Kind Regards,
Simon
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL LINE CONTINUATION ISSUE 12 31
I Need a T-SQL Query (Union or Intersect, or...?) 6 39
cannot connect to sqlserver 8 25
How to search for strings inside db views 4 24
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now