Hopefully this will be easy - an order by quesion

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hopefully this will be easy - an order by quesion

Author

Topic

loydall
Starting Member

33 Posts

Posted - 2010-09-08 : 07:01:06

Hi - lets say I have a table of categories that looks like this

ID | Name | ParentCategory
----------------------------
1 |Sport | 0
2 |Football | 1
3 |Cricket | 1
4 |Films | 0
5 |Thriller | 4
6 |Running | 1
7 |Comedy | 4

So - here, both Sport and Films are top level categories and the other records are children of those categories

I want to be able to select from this table so the results are like this:

Sport
Football
Cricket
Running
Films
Thriller
Comedy

So the results take the first parent record and then any child records are grouped beneath it

Also worth pointing out that a child record can have children of its own so we'd need to display those child records under its own parent as well (if that makes sense)

Any ideas?
Thanks.

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2010-09-08 : 09:06:04

That's not easy but not hard either.

with RecursionCTE (RecordID,ParentRecordID,SortOrder,Name)
 as
 (
   select RecordID,ParentRecordID,cast(RecordID as varchar(10)),Name
      from Sample1
      where ParentRecordID = 0
   union all
   select R1.RecordID,
          R1.ParentRecordID,
          R2.SortOrder + '/' + cast(RecordID as varchar(10)),          R1.Name
      from Sample1 as R1
     join RecursionCTE as R2 on R1.ParentRecordID = R2.RecordID
  )
select * from RecursionCTE order by SortOrder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources