Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-08-14
41
Medium Priority
?
1,943 Views
Last Modified: 2014-08-18
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
0
Comment
Question by:sweoff
  • 21
  • 16
  • 3
  • +1
41 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40260880
Please give us a more detailed data mockup of what you're trying to pull off here.
0
 

Author Comment

by:sweoff
ID: 40260892
Its in the question under Example: . That would be a true representation of the what the data would look like
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40260895
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 66

Expert Comment

by:Jim Horn
ID: 40260900
>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
 
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40260924
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
 

Author Comment

by:sweoff
ID: 40261009
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261018
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40261019
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
 

Author Comment

by:sweoff
ID: 40261040
Example.jpg
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261269
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
 

Author Comment

by:sweoff
ID: 40261549
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261642
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
 

Author Comment

by:sweoff
ID: 40261673
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261695
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
 

Author Comment

by:sweoff
ID: 40261707
Example.jpg
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261800
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
 

Author Comment

by:sweoff
ID: 40261812
Same error
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40261818
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
 

Author Comment

by:sweoff
ID: 40261876
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40262027
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
 

Author Comment

by:sweoff
ID: 40262058
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40263343
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
 

Author Comment

by:sweoff
ID: 40263359
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
 

Author Comment

by:sweoff
ID: 40263366
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40263532
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
 

Author Comment

by:sweoff
ID: 40263560
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40263613
https://documentation.devexpress.com/#AspNet/CustomDocument3942

Set it to virtual mode.  Load on demand and you're fine.
0
 

Author Comment

by:sweoff
ID: 40263650
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40263696
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
 

Author Comment

by:sweoff
ID: 40263805
kyle... I know what your saying and its not going to work... I've already tried it.
0
 
LVL 41

Accepted Solution

by:
Kyle Abrahams earned 2000 total points
ID: 40263889
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
 

Author Closing Comment

by:sweoff
ID: 40267820
Thank you very much! I know this one was a tuff one
0
 

Author Comment

by:sweoff
ID: 40268081
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
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40268537
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
 

Author Comment

by:sweoff
ID: 40268556
I tried that but because TopPath gets the first order it doesn't work
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40268579
in that case you should order by
the substring of toppath to the last '/' in topPath
then by length of toppath
0
 

Author Comment

by:sweoff
ID: 40268601
mmmm.... substring to length of ??
0
 
LVL 41

Expert Comment

by:Kyle Abrahams
ID: 40268628
something like:

order by
substring(TopPath,1,REVERSE(SUBSTRING(REVERSE(TopPath,0,CHARINDEX('/',REVERSE(TopPath))))),
len(topPath),
TopPath
0
 

Author Comment

by:sweoff
ID: 40268644
I'm sorry I'm still confused.... Do you have a complete example? This doesn't make since
0
 

Author Comment

by:sweoff
ID: 40268684
If I'm looking at this correct. Your splitting the TopPath by "/", but its still RegcodeCodeID and Not RegCode... Maybe I'm wrong..
0
 

Author Comment

by:sweoff
ID: 40268810
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

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…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 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