This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.
COURSE of the MONTH
12 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
ID and parentID - how to sort?
Posted on 2002-06-21
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3 Comments
Active today
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
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
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
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
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses
Course of the Month12 days, 21 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.