Jesper Christensen
asked on
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.ArtiklerKomment
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.ArtiklerKomment
)
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
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.ArtiklerKomment
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.ArtiklerKomment
)
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
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
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
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, pathASKER
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.
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.
ASKER
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.
What do you mean by: its desc by first, then asc by second and third values in the path.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
>> 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
ASKER
Very nice. Thumbs up for understanding my tricky description :)
>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.