Sort by nodes

The data is as follows:
Orderno parentno descript
2000007 Sun Tzu Lao Li
2000006 Old Wang son
2000005 Old Zhang son
2000004 old Li son
2000003 null
2000002 null Lao Zhang
2000001 null old King
2000000 null Lao Li

Sorting rules:
1. sort by orderno in descending order
2. Associated orders must be placed behind

After sorting, it should be as follows:
Orderno parentno descript
2000007 Sun Tzu Lao Li
2000004 old Li son
2000000 null Lao Li
2000006 Old Wang son
2000001 null old King
2000005 Old Zhang son
2000002 null Lao Zhang
2000003 null

----------------------------------------------------------------------------------

-- Author: htl258 (Tony)

-- Date: 11:40:00

-- Version: Microsoft SQL Server 2008 (RTM)-10.0.1600.22 (Intel x86)

-- Jul 9 2008 14:43:34

-- Copyright (c) 1988-2008 Microsoft Corporation

-- Developer Edition on Windows NT 5.1 <x86> (build 2600: Service Pack 2)

-- Blog: http://blog.csdn.net/htl258

----------------------------------------------------------------------------------

--> Generate a test data table: [TB]

If object_id ('[TB]') is not null

Drop table [TB]

Go

Create Table [TB] ([orderno] [int], [parentno] [int], [descript] [nvarchar] (10 ))

Insert into [TB]

Select '123', '123', 'Lee Sun Tzu 'Union all

Select '123', '123', 'old Wang sons' Union all

Select '123', '123', 'old Zhang sons' Union all

Select '123', '123', 'old Lee sons' Union all

Select '20140901', null, null Union all

Select '123', null, 'zhang' Union all

Select '123', null, 'old Wang 'Union all

Select '123', null, 'lily'

 

-- Select * from [TB]

 

--> The SQL query is as follows:

; With T

(

Select *, PX = cast (row_number () over (order by orderno DESC) as varbinary)

From TB

Where not exists (

Select 1

From TB

Where parentno = A. orderno

)

Union all

Select a. *, cast (

PX + Cast (

Row_number () over (partition by A. parentno order by A. orderno DESC) as varbinary

) As varbinary

)

From TB

Join T B

On a. orderno = B. parentno

)

Select orderno, parentno, descript

From t

Order by PX

/*

Orderno parentno descript

--------------------------------

2000007 Sun Tzu Lao Li

2000004 old Li son

2000000 null Lao Li

2000006 Old Wang son

2000001 null old King

2000005 Old Zhang son

2000002 null Lao Zhang

2000003 null

 

(Eight rows are affected)

*/

 

------------- Libin_ftsafe

Create Table Test (orderno varchar (10), parentno varchar (10), descript varchar (10 ))
Insert into test select '1234568', '1234568', 'Lee Sun Tzu'
Insert into test select '123', '123', 'old Wang son'
Insert into test select '000000', '000000', 'old Zhang sons'
Insert into test select '1234568', '1234568', 'Lee's son'
Insert into test select '200', null, null
Insert into test select '123', null, 'la'
Insert into test select '123', null, 'old Wang'
Insert into test select '123', null, 'lily'
Go

Create Function f_str (@ orderno varchar (10 ))
Returns varchar (1000)
As
Begin
Declare @ RET varchar (1000), @ childno varchar (10)
Select @ ret = orderno, @ childno = orderno from test where orderno = @ orderno

While @ rowcount <> 0
Begin
Set @ orderno = @ childno

Select @ ret = orderno + @ ret, @ childno = orderno from test where parentno = @ orderno
End

Return @ RET + 'Z'
End
Go

Select * from test order by DBO. f_str (orderno) DESC
/*
Orderno parentno descript
------------------------------
2000007 Sun Tzu Lao Li
2000004 old Li son
2000000 null Lao Li
2000006 Old Wang son
2000001 null old King
2000005 Old Zhang son
2000002 null Lao Zhang
2000003 null
*/
Go

Drop function f_str
Drop Table Test
Go

-----------

---------------------------------
-- Author: liangck xiaoliang
---------------------------------

--> Generate Test Data: [T]
If object_id ('[t]') is not null drop table [T]
Create Table [T] (orderno int, parentno int, descript varchar (8 ))
Insert into [T]
Select 2000007,2000004, 'Lee Sun Tzu 'Union all
Select 2000006, 2000001, 'old Wang sons' Union all
Select 2000005, 2000002, 'old zhangs' Union all
Select 2000004,2000000, 'old Lee sons' Union all
Select 2000003, null, null Union all
Select 2000002, null, 'login' Union all
Select 2000001, null, 'old Wang 'Union all
Select 2000000, null, 'lily'

-- SQL query:

Go

Create Function DBO. sortorderno (@ orderno INT)
Returns varchar (1000)
As
Begin
Declare @ Re varchar (1000 );
Select @ Re = @ orderno;

Select
@ Re = rtrim (orderno)
From t
Where parentno = @ orderno

While @ rowcount> 0
Begin
Select
@ Re = rtrim (orderno)
From t
Where parentno = left (@ Re, charindex (',', @ Re + ',')-1 );
End

Return @ Re
End
Go

Select * from t
Order by DBO. sortorderno (orderno) DESC, orderno DESC

Go
Drop table t
Drop function DBO. sortorderno

/*
Orderno parentno descript
------------------------------
2000007 Sun Tzu Lao Li
2000004 old Li son
2000000 null Lao Li
2000006 Old Wang son
2000001 null old King
2000005 Old Zhang son
2000002 null Lao Zhang
2000003 null

(Eight rows are affected)

*/

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/htl258/archive/2009/04/15/4075281.aspx