order result - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

order result

Author

Topic

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-07-02 : 13:46:23

Hi I have the following table:



ParentID  	 DataID  	 Name  	 

-10483061       10487806        Development robert 
-10483061       10487818        Standard Key User Anita
10487806        10488705        SUB Development robert

I want to change the order like this:

Development robert
SUB Development robert
Standard Key User Anita

Dataid and parentid show the dependency. I need to go through the list, if an entry has children (parentid = dataid) the children should be listed directly after that entry. Is there a way how I can do it?

Kind regards,

Lara

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-02 : 14:52:45

If level depth is max 2 (no grandchildren), this might work for you:

with yourtable (parentid,dataid,name)
  as (          select -10483061,10487806,'Developement robert'
      union all select -10483061,10487818,'Standard Key User Anita'
      union all select  10487806,10488705,'SUB Developement robert'
     )
select a.*
  from yourtable as a
       left outer join yourtable as b
                    on b.dataid=a.parentid
 order by case when b.dataid is null then a.dataid else b.dataid end

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-07-02 : 15:38:44

Hi thanks,

that was very helpful...can you explain the order clause for me? why is it b.dataid is null?


with yourtable (parentid,dataid,name)
  as (          select -10483061,10487806,'Developement robert'
      union all select -10483061,10487818,'Standard Key User Anita'
      union all select  -10483061,10487840,'Development Team User Ranacher'
	  union all select  10488557,10487852,'Development Project Teamuser Anita'
	  union all select  -10483061,10487864,'Standard Team User Anita'
	  union all select  10488142,10487872,'SUB Development Keyuser Anita'
	  union all select  -10483061,10488142,'Development Key User Anita'
	  union all select  -10483061,10488165,'Development Team User robert'
	  union all select  -10483061,10488178,'Standard Team User Ranacher'
	  union all select  10488574,10488186,'SUB Development Key User Ranacher'
	  union all select  -10483061,10488557,'POSD Development Project'
	  union all select  -10483061,10488574,'Development Key User Ranache'
	  union all select  -10483061,10488595,'Standard Team User Rober'
	  union all select  -10483061,10488679,'Standard Key User Ranache'
	  union all select  -10483061,10488687,'POSD Standard Project key user robert'
	  union all select  10487806,10488705,'SUB Development Key User robert'
     )

I tried it with more examples and it works except for one case:

This entry is not ordered correctly:

10488142 10487872 SUB Development Keyuser Anita

Can you see why, beacause there is a parent child relation?

Kind regards,

Lara

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-07-02 : 16:00:43

The order clause in plain words is: order by parents id if it exists, otherwise order by it's own id.

In order for your second example to work, append parentid ot the order clause - like this:

with yourtable (parentid,dataid,name)
  as (          select -10483061,10487806,'Developement robert'
      union all select -10483061,10487818,'Standard Key User Anita'
      union all select -10483061,10487840,'Development Team User Ranacher'
      union all select  10488557,10487852,'Development Project Teamuser Anita'
      union all select -10483061,10487864,'Standard Team User Anita'
      union all select  10488142,10487872,'SUB Development Keyuser Anita'
      union all select -10483061,10488142,'Development Key User Anita'
      union all select -10483061,10488165,'Development Team User robert'
      union all select -10483061,10488178,'Standard Team User Ranacher'
      union all select  10488574,10488186,'SUB Development Key User Ranacher'
      union all select -10483061,10488557,'POSD Development Project'
      union all select -10483061,10488574,'Development Key User Ranache'
      union all select -10483061,10488595,'Standard Team User Rober'
      union all select -10483061,10488679,'Standard Key User Ranache'
      union all select -10483061,10488687,'POSD Standard Project key user robert'
      union all select  10487806,10488705,'SUB Development Key User robert'
     )
select a.*
  from yourtable as a
       left outer join yourtable as b
                    on b.dataid=a.parentid
 order by case when b.dataid is null then a.dataid else b.dataid end
         ,a.parentid

musclebreast
Yak Posting Veteran

77 Posts

Posted - 2014-07-02 : 16:52:50

hi,

thanks, it works.:) ...I need to play around more with that special order by codes in order to understand such method completely. I nver stop learning here. Thanks..

Cheers,

Lara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources