Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql problem with order / group

Posted on 2013-01-25
8
Medium Priority
?
287 Views
Last Modified: 2013-01-25
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
0
Comment
Question by:bongii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38820512
<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
 
LVL 35

Expert Comment

by:David Todd
ID: 38820527
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38820575
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:bongii
ID: 38820641
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
 

Author Comment

by:bongii
ID: 38820646
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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 38820651
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
 
LVL 35

Expert Comment

by:David Todd
ID: 38820658
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
 

Author Closing Comment

by:bongii
ID: 38820682
Very nice. Thumbs up for understanding my tricky description :)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

670 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