|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Sorting a parent-child listID CatName ParentID 1 Cat 1 NULL 2 Cat 2 NULL 3 Cat 1.1 1 4 Cat 1.2 1 5 Cat 2.1 2 6 Cat 3 NULL 7 Cat 3.1 6 I want to sort this (Select * from tblCat) like this 1 Cat 1 3 Cat 1.1 4 Cat 1.2 2 Cat 2 5 Cat 2.1 6 Cat 3 7 Cat 3.1 Can I do this in a SQL statement? Sonny Hi Sonny,
I think if you are entering data like catname = Cat 1, cat 1.1 cat 1.2 etc then a simple query can do this select * from yourtable order by catname With Warm regards Jatinder Singh http://jatindersingh.blogspot.com Sonny Sablan wrote: Show quoteHide quote > tblCat > > ID CatName ParentID > > 1 Cat 1 NULL > 2 Cat 2 NULL > 3 Cat 1.1 1 > 4 Cat 1.2 1 > 5 Cat 2.1 2 > 6 Cat 3 NULL > 7 Cat 3.1 6 > > > I want to sort this (Select * from tblCat) like this > > 1 Cat 1 > 3 Cat 1.1 > 4 Cat 1.2 > 2 Cat 2 > 5 Cat 2.1 > 6 Cat 3 > 7 Cat 3.1 > > Can I do this in a SQL statement? > > Sonny No, the catnames are not that simple...
The names could be anything, below is just an example. So how would I do it if the catnames could be be ordered? Sonny Show quoteHide quote "jsfromynr" <jatinder.si***@clovertechnologies.com> wrote in message news:1149834705.061503.82470@i40g2000cwc.googlegroups.com... > Hi Sonny, > > I think if you are entering data like catname = Cat 1, cat 1.1 cat 1.2 > etc then a simple query can do this > > select * from yourtable order by catname > > With Warm regards > Jatinder Singh > http://jatindersingh.blogspot.com > > Sonny Sablan wrote: >> tblCat >> >> ID CatName ParentID >> >> 1 Cat 1 NULL >> 2 Cat 2 NULL >> 3 Cat 1.1 1 >> 4 Cat 1.2 1 >> 5 Cat 2.1 2 >> 6 Cat 3 NULL >> 7 Cat 3.1 6 >> >> >> I want to sort this (Select * from tblCat) like this >> >> 1 Cat 1 >> 3 Cat 1.1 >> 4 Cat 1.2 >> 2 Cat 2 >> 5 Cat 2.1 >> 6 Cat 3 >> 7 Cat 3.1 >> >> Can I do this in a SQL statement? >> >> Sonny > Well how will I know that cat 1.2 comes after cat 1.1 if the name doesn't
suggest anything?, Both has parent ID 1. Can you give a better explanation on how it will be ordered? Show quoteHide quote "Sonny Sablan" wrote: > No, the catnames are not that simple... > > The names could be anything, below is just an example. So how would I do it > if the catnames could be be ordered? > > Sonny > > "jsfromynr" <jatinder.si***@clovertechnologies.com> wrote in message > news:1149834705.061503.82470@i40g2000cwc.googlegroups.com... > > Hi Sonny, > > > > I think if you are entering data like catname = Cat 1, cat 1.1 cat 1.2 > > etc then a simple query can do this > > > > select * from yourtable order by catname > > > > With Warm regards > > Jatinder Singh > > http://jatindersingh.blogspot.com > > > > Sonny Sablan wrote: > >> tblCat > >> > >> ID CatName ParentID > >> > >> 1 Cat 1 NULL > >> 2 Cat 2 NULL > >> 3 Cat 1.1 1 > >> 4 Cat 1.2 1 > >> 5 Cat 2.1 2 > >> 6 Cat 3 NULL > >> 7 Cat 3.1 6 > >> > >> > >> I want to sort this (Select * from tblCat) like this > >> > >> 1 Cat 1 > >> 3 Cat 1.1 > >> 4 Cat 1.2 > >> 2 Cat 2 > >> 5 Cat 2.1 > >> 6 Cat 3 > >> 7 Cat 3.1 > >> > >> Can I do this in a SQL statement? > >> > >> Sonny > > > > > Are you using SQL 2000 or 2005?
I am assuming that you want to order by ID for each top node (parent is null) and then display the children of that node, sorted by ID, with each of their children listed below them, etc? 1 1.1 1.2 1.2.1 1.2.2 1.2.2.1 1.3 1.4 1.4.1 1.4.1.1 1.4.1.2 etc... I believe you need a recursive query to do this. SQL 2005 lets you use CTEs (Common Table Expressions) for recursive SQL, but with 2000 you need to use a stored procedure. Show quoteHide quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:FE1F4D2C-3D84-44B8-AEAA-CCA4C88D9FE1@microsoft.com... > Well how will I know that cat 1.2 comes after cat 1.1 if the name doesn't > suggest anything?, Both has parent ID 1. Can you give a better explanation on > how it will be ordered? > > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > > > "Sonny Sablan" wrote: > > > No, the catnames are not that simple... > > > > The names could be anything, below is just an example. So how would I do it > > if the catnames could be be ordered? > > > > Sonny > > > > "jsfromynr" <jatinder.si***@clovertechnologies.com> wrote in message > > news:1149834705.061503.82470@i40g2000cwc.googlegroups.com... > > > Hi Sonny, > > > > > > I think if you are entering data like catname = Cat 1, cat 1.1 cat 1.2 > > > etc then a simple query can do this > > > > > > select * from yourtable order by catname > > > > > > With Warm regards > > > Jatinder Singh > > > http://jatindersingh.blogspot.com > > > > > > Sonny Sablan wrote: > > >> tblCat > > >> > > >> ID CatName ParentID > > >> > > >> 1 Cat 1 NULL > > >> 2 Cat 2 NULL > > >> 3 Cat 1.1 1 > > >> 4 Cat 1.2 1 > > >> 5 Cat 2.1 2 > > >> 6 Cat 3 NULL > > >> 7 Cat 3.1 6 > > >> > > >> > > >> I want to sort this (Select * from tblCat) like this > > >> > > >> 1 Cat 1 > > >> 3 Cat 1.1 > > >> 4 Cat 1.2 > > >> 2 Cat 2 > > >> 5 Cat 2.1 > > >> 6 Cat 3 > > >> 7 Cat 3.1 > > >> > > >> Can I do this in a SQL statement? > > >> > > >> Sonny > > > > > > > > > well if its sorted by ID, then its fairly simple.
Select * from tblCat order by coalesce(ParentID,ID) Won't this work? Show quoteHide quote "Jim Underwood" wrote: > Are you using SQL 2000 or 2005? > > I am assuming that you want to order by ID for each top node (parent is > null) and then display the children of that node, sorted by ID, with each of > their children listed below them, etc? > > 1 > 1.1 > 1.2 > 1.2.1 > 1.2.2 > 1.2.2.1 > 1.3 > 1.4 > 1.4.1 > 1.4.1.1 > 1.4.1.2 > etc... > > I believe you need a recursive query to do this. SQL 2005 lets you use CTEs > (Common Table Expressions) for recursive SQL, but with 2000 you need to use > a stored procedure. > > "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message > news:FE1F4D2C-3D84-44B8-AEAA-CCA4C88D9FE1@microsoft.com... > > Well how will I know that cat 1.2 comes after cat 1.1 if the name doesn't > > suggest anything?, Both has parent ID 1. Can you give a better explanation > on > > how it will be ordered? > > > > -- > > -Omnibuzz (The SQL GC) > > > > http://omnibuzz-sql.blogspot.com/ > > > > > > > > "Sonny Sablan" wrote: > > > > > No, the catnames are not that simple... > > > > > > The names could be anything, below is just an example. So how would I do > it > > > if the catnames could be be ordered? > > > > > > Sonny > > > > > > "jsfromynr" <jatinder.si***@clovertechnologies.com> wrote in message > > > news:1149834705.061503.82470@i40g2000cwc.googlegroups.com... > > > > Hi Sonny, > > > > > > > > I think if you are entering data like catname = Cat 1, cat 1.1 cat 1.2 > > > > etc then a simple query can do this > > > > > > > > select * from yourtable order by catname > > > > > > > > With Warm regards > > > > Jatinder Singh > > > > http://jatindersingh.blogspot.com > > > > > > > > Sonny Sablan wrote: > > > >> tblCat > > > >> > > > >> ID CatName ParentID > > > >> > > > >> 1 Cat 1 NULL > > > >> 2 Cat 2 NULL > > > >> 3 Cat 1.1 1 > > > >> 4 Cat 1.2 1 > > > >> 5 Cat 2.1 2 > > > >> 6 Cat 3 NULL > > > >> 7 Cat 3.1 6 > > > >> > > > >> > > > >> I want to sort this (Select * from tblCat) like this > > > >> > > > >> 1 Cat 1 > > > >> 3 Cat 1.1 > > > >> 4 Cat 1.2 > > > >> 2 Cat 2 > > > >> 5 Cat 2.1 > > > >> 6 Cat 3 > > > >> 7 Cat 3.1 > > > >> > > > >> Can I do this in a SQL statement? > > > >> > > > >> Sonny > > > > > > > > > > > > > > > > Small change.. I always miss something :)
Select * from tblCat order by coalesce(ParentID,ID),ID Sonny, Let me know if this was what you wanted. This will work great if the tree is only one level deep, but if more levels
are added, I think this will break. Since the OP did not specify how many levels there may be, or exactly how the order should work, we can only guess. The following code shows two select statements, one which orders by parentId, ID, and one which presents a different order. The second one works only because I made the CAT column match the order I wanted, but it illustrates what the desired results may be. I think --CELKO-- is correct that this database design does not lend itself to tree queries, although CTEs would be able to accomplish it. Recursive stored procs, imo, are just a pita and more trouble than they are worth (although in 2000 the only way to get an employee/manager tree). create table testtree (id integer primary key , cat varchar (10) not null , parentID integer) go insert into testtree(id, parentid, cat) select 1, NULL, '1' union select 2, 1, '1.1' union select 3, 1, '1.2' union select 4, 1, '1.3' union select 5, 1, '1.4' union select 6, 3, '1.2.1' union select 7, 3, '1.2.2' union select 8, 5, '1.4.1' union select 9, 7, '1.2.2.1' union select 10, 8, '1.4.1.1' union select 11, 8, '1.4.1.2' go select * from testtree order by coalesce(ParentID,ID),ID select * from testtree order by cat drop table testtree Show quoteHide quote "Omnibuzz" <Omnib***@discussions.microsoft.com> wrote in message news:5B12EC7A-9655-42D3-B6F2-BB28ECA008B9@microsoft.com... > Small change.. I always miss something :) > > Select * from tblCat order by coalesce(ParentID,ID),ID > > Sonny, Let me know if this was what you wanted. > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ > > >> Can I do this in a SQL statement? << Get a copy of TREES & HIERARCHIES IN SQL. Look up the Nested Setsmodel. You can avoid all procedural code, such as recursion and loops, by picking the proper table design. Learn to think in sets and not linked lists.  
Other interesting topics
UNION problem
Optimize the Query process is blocking itself SELECT Query With Sum How to construct a like compariso when there are special characts Procedure/cursor question about returning results truth or fiction about writing correct queries Running MS SQL query from Excel VBA UDF's in SQL2005 Need help understanding fn_get_sql and the stmt_start and stmt_end columns |
|||||||||||||||||||||||
