SQL Query to Order by Field Name with ID & ParentID in the same table

I need help with writing a query that will order by fieldname "Name" were the table as an ID and ParentID. So I need the query to order the name based on the Parent to child relation. The query can not just go 2 or 3 levels deep... It has to be strong enough to go all the way through the hierarchy.

Example:
ID                 Parent ID                   Name
1                   0                                 1.3.5
2                   1                                  2.65
3                   1                                  2.66
4                   0                                  1.3.6
5                   4                                  2.65
6                   5                                  2.65A
7                   4                                  2.66
....
........
..............
and so fourth
sweoffAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Please give us a more detailed data mockup of what you're trying to pull off here.
0
sweoffAuthor Commented:
Its in the question under Example: . That would be a true representation of the what the data would look like
0
Kyle AbrahamsSenior .Net DeveloperCommented:
sample query:
;with cte as
(
select u.userid, m.userid ManagerId, u.UserName, m.username ManagerName from users u
left join users  m on m.userid = u.managerid)

select * from cte
order by userid, managerid

Open in new window


userId is your childID, ManagerID is your ParentID, and I included names for each.  

altered query:
;with cte as
(
select t.ID, m.ID ParentID, u.Name, m.Name as parentName from <TABLE> u
left join <TABLE> m on m.id = u.ParentID)

select * from cte
order by id, ParentID

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>I need help with writing a query that will order by fieldname "Name"
If this is what you need then it's just SELECT * FROM table_name ORDER BY [Name], but that doens't seem to jive with the description of ID and Parent ID ... 2 or 3 levels deep.   So you'll need to spell that out in further detail.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'm with Jim. We need more information. Maybe examples also will help.

Just for now, what's the problem with the simple query like the following one?

SELECT Name
FROM MyTable
ORDER BY ParentID, ID, Name

Open in new window


I mean, what this query should do more to be the solution that you want?
0
sweoffAuthor Commented:
I'm not sure what you guys are looking for...... The example data explains exactly what I need. Kyles seems close but his examples not very understanding.... He t, m, and u in one example and m, and u in another. He has <table> reference which doesn't even make since.... The ID's (ID and ParentID) are in the same table.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Can you post an example of your data?

select top 10 * from <table>
order by id, parentID

I'm thinking you have something like this:

Parent ID     ID
   1                 2
   2                 3


You never provided the name of your table you're working with so <table> is a place holder for your tablename.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Tell you what.  I see one sample data set.

Show us two:  One for what your data looks like now, and another for what you want returned in the query.
0
sweoffAuthor Commented:
Example.jpg
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Okay, so that's your existing, what do you want your output to be?

And I'm assuming that 1209 (or another regCodeID) could be another parent as well?
;with cte as
(
select R.RegCodeId,  P.regCodeID ParentID, R.RegCode, P.RegCode ParentRegCode 
from tblRegulations R
left join tblRegulations P on P.regCodeID = R.RegCodeParentID)

select * from cte
order by RegCodeID, ParentId

Open in new window

0
sweoffAuthor Commented:
Example.jpg
So RegCode 8-6-418 Needs to be under its parent 37.01 (Parent).  Each set of children needs to be order to. There can been multiple children and children can be parents to other children.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I believe you need a recursive function:
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

Essentially you want to sort first by level, then by parentRegCode, then By RegCode.
0
sweoffAuthor Commented:
Kyle, Yes that does seem to be what I need... But based on that example they are only accounting for 2 levels (Parent + 1) and level is seemingly hard coded. I need it to build continuously and not just 2 levels.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
No.  

If you look at the example, it's a recursive query.

You start at level 0
and join to the next level, using level + 1 as a marker.
The end results will give you what you want.

Something like:
;with cte as
(
select R.RegCodeID,  0 ParentID, R.RegCode, R.RegCode, 0 as level
from 
tblRegulations R
where RegCodeParentID = 0
union all 
select R.RegCodeId,  R.RegCodeParentID ParentID, R.RegCode, P.RegCode ParentRegCode , level + 1
from tblRegulations R
join cte P on P.regCodeID = R.RegCodeParentID)

select * from cte
order by level, ParentRegCode, RegCode

Open in new window

0
sweoffAuthor Commented:
Example.jpg
0
Kyle AbrahamsSenior .Net DeveloperCommented:
Had a typo . . .

;with cte as
(
select R.RegCodeID,  0 ParentID, R.RegCode, R.RegCode ParentRegCode , 0 as level
from 
tblRegulations R
where RegCodeParentID = 0
union all 
select R.RegCodeId,  R.RegCodeParentID ParentID, R.RegCode, P.RegCode , level + 1
from tblRegulations R
join cte P on P.regCodeID = R.RegCodeParentID)

select * from cte
order by level, ParentRegCode, RegCode

Open in new window

0
sweoffAuthor Commented:
Same error
0
Kyle AbrahamsSenior .Net DeveloperCommented:
And one more:

;with cte as
(
select R.RegCodeID,  RegCodeParentID ParentID, R.RegCode, R.RegCode ParentRegCode , 0 as level
from
tblRegulations R
where RegCodeParentID = 0
union all
select R.RegCodeId,  R.RegCodeParentID ParentID, R.RegCode, P.RegCode , level + 1
from tblRegulations R
join cte P on P.regCodeID = R.RegCodeParentID)

select * from cte
order by level, ParentRegCode, RegCode
0
sweoffAuthor Commented:
Ok this is the results from your query....   I'm really trying to describe what I need. So I'm trying to Order the RegCode off the results. If you look at RegCodeID (1209) its a child of (37.01). It should not be showing up under RegCode(9.16). It needs to show under ParentRegCode(37.01). I'm really not sure what this is doing.... Back to my first request for help ....

I need it to select in Order of the RegCode in 1 column, but you have to use the RegCodeID and ParentRegCodeID to do that.

Example.jpg
0
Kyle AbrahamsSenior .Net DeveloperCommented:
If I'm understanding you right you want something like

RegCodeID        ParentID      RegCode                 ParentRegCode
  886                   30                 37.01                       J
  1209                 886               27-101-105(b)          37.01
  1210                 886               27-101-202(1)          37.01
...  REST of 886 children

  887                     35             98.7                                             K
 ChildOf(887)        887                 28-100-200         98.7


If that's the case this would need to be a complicated loop.  What's your end result that you're trying to show this in?
0
sweoffAuthor Commented:
DING... DING.... DING....   Yes that's EXACTLY what I want. I'm build a master list for a combo box that will do incremental filtering on the data.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I don't see a way to do this in SQL without jumping through some major hoops and will kill performance.  How many records are you talking about and how often do the regs change?  

A tree view might be better for you in that you can establish the parent child hierarchy, and make it more navigable for your users.
0
sweoffAuthor Commented:
Kyle,

I agree with Tree List but I'm using DevExpress tool set and it doesn't support incremental filtering. Only the combo box does.
0
sweoffAuthor Commented:
Regs don't change that often, but can change but a few times a years... more so adding a few new ones each year.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I'm hesitant to mention it because it's a nightmare to manage, but you could have a master sort Id column.

In that way you could order by just that field.  But you would need to do this manually and every time new regs are added.    Again, it is a solution, I wouldn't do it.

The other option is to use both a tree view and the combo box.

Use the treeview to represent your hierarchy.   Use the combo box and list all regs in alphabetical order.  If you're using incremental filtering, it's because the person knows what reg they want, so the hierarchy becomes less important.

After they select the reg in the combo box, find it in the treeview and this way you get the hierarchy displayed there and it's always in sync.
0
sweoffAuthor Commented:
Kyle,
 I see your point but I don't want to load tree with thousands of possibilities. This is a web based application and would bog it down
0
Kyle AbrahamsSenior .Net DeveloperCommented:
https://documentation.devexpress.com/#AspNet/CustomDocument3942

Set it to virtual mode.  Load on demand and you're fine.
0
sweoffAuthor Commented:
Ok... but I don't want the user to have to sort and pick through a tree list. This is data input and needs to be extremely quick and efficient
0
Kyle AbrahamsSenior .Net DeveloperCommented:
that's why I'm saying use the combo box in conjunction with the tree view.  You get the best of both worlds, and don't have to worry about the hierarchy in the combobox.
0
sweoffAuthor Commented:
kyle... I know what your saying and its not going to work... I've already tried it.
0
Kyle AbrahamsSenior .Net DeveloperCommented:
I added top path which I think will work.
I used the IDs because I'm not sure how many levels you go down.  You can use the RegCode if need be.

;with cte as
(
select R.RegCodeID,  RegCodeParentID ParentID, R.RegCode, R.RegCode ParentRegCode , 0 as level, cast(r.RegCodeID as varchar(max)) TopPath
from 
tblRegulations R
where RegCodeParentID = 0
union all 
select R.RegCodeId,  R.RegCodeParentID ParentID, R.RegCode, P.RegCode , level + 1, TopPath + '/' + cast(r.RegCodeID as varchar(max))
from tblRegulations R
join cte P on P.regCodeID = R.RegCodeParentID)

select * from cte
order by TopPath

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sweoffAuthor Commented:
Thank you very much! I know this one was a tuff one
0
sweoffAuthor Commented:
Kyle,

I have some regcode's that start 10.03.. on so on and then I have some that start 2.03 and so on... The length needs to be accounted for when you do the ordering. so that 2.03 is before 10.03. What do I need to account for that within the query?
0
Kyle AbrahamsSenior .Net DeveloperCommented:
order by regcode after.

select * from cte
order by TopPath, RegCode

if you really needed the length:

select * from cte
order by TopPath, len(regCode)
0
sweoffAuthor Commented:
I tried that but because TopPath gets the first order it doesn't work
0
Kyle AbrahamsSenior .Net DeveloperCommented:
in that case you should order by
the substring of toppath to the last '/' in topPath
then by length of toppath
0
sweoffAuthor Commented:
mmmm.... substring to length of ??
0
Kyle AbrahamsSenior .Net DeveloperCommented:
something like:

order by
substring(TopPath,1,REVERSE(SUBSTRING(REVERSE(TopPath,0,CHARINDEX('/',REVERSE(TopPath))))),
len(topPath),
TopPath
0
sweoffAuthor Commented:
I'm sorry I'm still confused.... Do you have a complete example? This doesn't make since
0
sweoffAuthor Commented:
If I'm looking at this correct. Your splitting the TopPath by "/", but its still RegcodeCodeID and Not RegCode... Maybe I'm wrong..
0
sweoffAuthor Commented:
OK.... I figured out what you were trying to do... Below working version

;with cte as
(
select R.RegCodeID, RegCodeParentID ParentID, R.RegCode, R.RegCode ParentRegCode , 0 as level, CAST(r.RegCode as varchar(max)) TopPath
from
tblRegulations R
where RegCodeParentID = 0 AND Active = 1
union all
select R.RegCodeId,  R.RegCodeParentID ParentID, R.RegCode, P.RegCode , level + 1, TopPath + '|' + cast(r.RegCode as varchar(max))
from tblRegulations R
join cte P on P.regCodeID = R.RegCodeParentID
WHERE Active = 1)

select * from cte
ORDER BY CASE WHEN CHARINDEX('|', TopPath, 0) > 0 THEN LEN(SUBSTRING(TopPath, 0, CHARINDEX('|', TopPath, 0))) ELSE LEN(TopPath) END, TopPath
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.