sql problem with order / group

Hi.

I have this script, but would like to order by ID with parentID null desc.
"I tried  order by id desc" but it dosent work. Can anybody help?

 with product_hierarchy as
(
select top 1000 ID, ArtikelID, parentID, tekst ,1 lev , cast(id as varchar(max)) as path from
H2581_Shop.ArtiklerKommentar where parentid is null and artikelid = 1 order by id desc
union all
select p.ID, p.ArtikelID, p.parentID, p.tekst ,ph.lev+1 , ph.path+'.'+ cast(p.id as varchar(max)) as path from H2581_Shop.ArtiklerKommentar p join product_hierarchy ph on ph.id = p.parentid
)
select * from product_hierarchy order by path


Please look at attrached file.
It shows my table.

I want to order the table so the order would be:
ID:
7
9
1
2
4
6
3
8
5
Udklip.PNG
Jesper ChristensenProgrammerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
try this, I had not had a chance to build a test scenario the first time and used the wrong ID for my MasterParent value
 with product_hierarchy as 
(
select top 1000 ID, ArtikelID, parentID, tekst ,1 lev , cast(id as varchar(max)) as path, ID as MasterParentID, 0 As ParentFirst from 
H2581_Shop.ArtiklerKommentar where parentid is null and artikelid = 1 order by id desc
union all
select p.ID, p.ArtikelID, p.parentID, p.tekst ,ph.lev+1 , ph.path+'.'+ cast(p.id as varchar(max)) as path, ph.MasterParentID, 1 As ParentFirst from H2581_Shop.ArtiklerKommentar p join product_hierarchy ph on ph.id = p.parentid
) 
select * from product_hierarchy order by MasterParentID desc, ParentFirst, path

Open in new window

Resulst in Results
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<air code>

>but would like to order by ID with parentID null desc.
Give this a whirl..

ORDER BY CASE WHEN ParentID IS NULL THEN 0 ELSE 1 END, ID

btw the series of numbers you provided doens't match the above > statement.
0
 
David ToddSenior DBACommented:
Hi,

Is this how you want to sort? - its desc by first, then asc by second and third values in the path.

ID path
7 7
9 7.9
1 1
2 1.2
4 1.2.4
6 1.2.6
3 1.3
8 1.3.8
5 1.5

Regards
  David
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Chris LuttrellSenior Database ArchitectCommented:
Your order by in the first query is not guaranteed to carry through to the final query, so try this to carry it through and order by the parents in decending order then the dependent records in ID order
 with product_hierarchy as 
(
select top 1000 ID, ArtikelID, parentID, tekst ,1 lev , cast(id as varchar(max)) as path, parentID as MasterParentID, 0 As ParentFirst from 
H2581_Shop.ArtiklerKommentar where parentid is null and artikelid = 1 order by id desc
union all
select p.ID, p.ArtikelID, p.parentID, p.tekst ,ph.lev+1 , ph.path+'.'+ cast(p.id as varchar(max)) as path, ph.MasterParentID, 1 As ParentFirst from H2581_Shop.ArtiklerKommentar p join product_hierarchy ph on ph.id = p.parentid
) 
select * from product_hierarchy order by MasterParentID desc, ParentFirst, path

Open in new window

0
 
Jesper ChristensenProgrammerAuthor Commented:
Hi guys.
Thank for reply, but I want the order of the output to be:

ID 7: ParentID is null
All childs with path 7*
ID 1: ParentID is null
All childs with path 1*

It is used to replies to articles and i want the newest reply first.
0
 
Jesper ChristensenProgrammerAuthor Commented:
dtodd: Yes thats how I want to sort.
What do you mean by: its desc by first, then asc by second and third values in the path.
0
 
David ToddSenior DBACommented:
Hi,

>> newest reply first
That you aren't doing as I read your data - newest conversation first, then first reply ... last reply.

Suggestion:
Split the path into root and path, then the order by becomes
order by
    root desc
    , path asc

hth
  David
0
 
Jesper ChristensenProgrammerAuthor Commented:
Very nice. Thumbs up for understanding my tricky description :)
0
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.

All Courses

From novice to tech pro — start learning today.