Complex SortOrder - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Complex SortOrder

Author

Topic

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 05:32:45

Hi,
this is the final output.

Declare @SortOrder Varchar(32)
Set @SortOrder = 'A-z'

Declare @SortOrder Varchar(32)
Set @SortOrder = 'A-z'
Declare @Sample Table
(
Title_Id Varchar(100),
TitleName Varchar(512),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Sample
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep' Union All
Select '2F00', '24 - 02' , 'FA74740C', 'E6A72981', 'Sea' Union All
Select '2F18', '24 - 02 - 1:00 A.M. - 2:00 A.M.' , 'E6A72981', 'EBE31EC6', 'Ep' Union All
Select '2F06', '24 - 02 - 1:00 P.M. - 2:00 P.M.' , 'E6A72981', 'C90041EA', 'Ep' Union All
Select '2F03', '24 - 02 - 10:00 A.M. - 11:00 A.M.' , 'E6A72981', '9DBD0274', 'Ep' Union All
Select '2F15', '24 - 02 - 10:00 P.M. - 11:00 P.M.' , 'E6A72981', '1D1A66CC', 'Ep' Union All
Select '2F04', '24 - 02 - 11:00 A.M. - 12:00 P.M.' , 'E6A72981', 'D102F5A8', 'Ep' Union All
Select '2F16', '24 - 02 - 11:00 P.M. - 12:00 A.M.' , 'E6A72981', '24FCCE23', 'Ep' Union All
Select '2F17', '24 - 02 - 12:00 A.M. - 1:00 A.M.' , 'E6A72981', 'A8279615', 'Ep' Union All
Select '3F00', '24 - 03', 'FA74740C', 'BCECCC49', 'Sea' Union All
Select '3F13', '24 - 03 - 1:00 A.M. - 2:00 A.M.' , 'BCECCC49', 'C71D3DA3', 'Ep' Union All
Select '3F01', '24 - 03 - 1:00 P.M. - 2:00 P.M.' , 'BCECCC49', '65756B84', 'Ep' Union All
Select '3F22', '24 - 03 - 10:00 A.M. - 11:00 A.M.' , 'BCECCC49', '060D9289', 'Ep' Union All
Select '3F10', '24 - 03 - 10:00 P.M. - 11:00 P.M.' , 'BCECCC49', 'D4914743', 'Ep' Union All
Select '3F23', '24 - 03 - 11:00 A.M. - 12:00 P.M.' , 'BCECCC49', '525633B7', 'Ep' Union All
Select '3F11', '24 - 03 - 11:00 P.M. - 12:00 A.M.' , 'BCECCC49', 'E0893040', 'Ep' Union All
Select '3F12', '24 - 03 - 12:00 A.M. - 1:00 A.M.' , 'BCECCC49', 'AE0D8BE3', 'Ep'

Select * From @Sample
order By Case when @SortOrder = 'A-z' then TitleName End

But My Requirement is like Tvd_Type = 'Ep' then it should be sort on Title_Id. and my output like this

Declare @Output Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Output
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep' Union All
Select '2F00', '24 - 02' , 'FA74740C', 'E6A72981', 'Sea' Union All
Select '2F03', '24 - 02 - 10:00 A.M. - 11:00 A.M.' , 'E6A72981', '9DBD0274', 'Ep' Union All
Select '2F04', '24 - 02 - 11:00 A.M. - 12:00 P.M.' , 'E6A72981', 'D102F5A8', 'Ep' Union All
Select '2F06', '24 - 02 - 1:00 P.M. - 2:00 P.M.' , 'E6A72981', 'C90041EA', 'Ep' Union All
Select '2F15', '24 - 02 - 10:00 P.M. - 11:00 P.M.' , 'E6A72981', '1D1A66CC', 'Ep' Union All
Select '2F16', '24 - 02 - 11:00 P.M. - 12:00 A.M.' , 'E6A72981', '24FCCE23', 'Ep' Union All
Select '2F17', '24 - 02 - 12:00 A.M. - 1:00 A.M.' , 'E6A72981', 'A8279615', 'Ep' Union All
Select '2F18', '24 - 02 - 1:00 A.M. - 2:00 A.M.' , 'E6A72981', 'EBE31EC6', 'Ep' Union All
Select '3F00', '24 - 03', 'FA74740C', 'BCECCC49', 'Sea' Union All
Select '3F01', '24 - 03 - 1:00 P.M. - 2:00 P.M.' , 'BCECCC49', '65756B84', 'Ep' Union All
Select '3F10', '24 - 03 - 10:00 P.M. - 11:00 P.M.' , 'BCECCC49', 'D4914743', 'Ep' Union All
Select '3F11', '24 - 03 - 11:00 P.M. - 12:00 A.M.' , 'BCECCC49', 'E0893040', 'Ep' Union All
Select '3F12', '24 - 03 - 12:00 A.M. - 1:00 A.M.' , 'BCECCC49', 'AE0D8BE3', 'Ep' Union all
Select '3F13', '24 - 03 - 1:00 A.M. - 2:00 A.M.' , 'BCECCC49', 'C71D3DA3', 'Ep' Union All
Select '3F22', '24 - 03 - 10:00 A.M. - 11:00 A.M.' , 'BCECCC49', '060D9289', 'Ep' Union All
Select '3F23', '24 - 03 - 11:00 A.M. - 12:00 P.M.' , 'BCECCC49', '525633B7', 'Ep'

Select * From @Output

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-17 : 06:30:38

[code]
try this once and check it
Select * FROM @Sample
ORDER By LEFT(titlename,CHARINDEX(' ',titlename)-1),tvd_type DESC,
Case
WHEN @SortOrder = 'A-z' AND tvd_type = 'ep' THEN title_id
WHEN @SortOrder = 'A-z' THEN TitleName
END
[/code]

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 07:01:53

Hi,

it shows the Error as 'Invalid length parameter passed to the LEFT or SUBSTRING function.'

i modified as

Select * FROM @Sample
ORDER By LEFT(titlename,CHARINDEX(' ',titlename)),tvd_type DESC,
Case
WHEN @SortOrder = 'A-z' AND tvd_type = 'ep' THEN title_id
WHEN @SortOrder = 'A-z' THEN TitleName
END

But output is not Correct.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-17 : 07:13:34

[code]
Declare @SortOrder Varchar(32)
Set @SortOrder = 'A-z'
Declare @Sample Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Sample
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep'

Select * FROM @Sample
ORDER By LEFT(titlename,CHARINDEX(' ',titlename)-1),tvd_type DESC,
Case
WHEN @SortOrder = 'A-z' AND tvd_type = 'ep' THEN title_id
WHEN @SortOrder = 'A-z' THEN TitleName
END

Declare @Output Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Output
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep'

Select * From @Output
[/code]
execute this one i am getting the same output as u expected

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 07:30:11

Hi,

This is the Same error Iam Getting 'Invalid length parameter passed to the LEFT or SUBSTRING function.
'

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 07:46:33

I have tried the above example from bklr and there was no error.
Did you copy and paste the example?

No, you're never too old to Yak'n'Roll if you're too young to die.

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 08:16:17

Hi,

please check this

Select '0F00','240','24C5F2D0','FA74740C','Ser'

There is no Extra Space '240' Not like '240 '

if no space then we get the Error like 'Invalid length parameter passed to the LEFT or SUBSTRING function.'

i replace with Full Data. please chk the SortOrder. its Failing.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-17 : 09:24:40

Then check this please:

Declare @SortOrder Varchar(32)
Set @SortOrder = 'A-z'
Declare @Sample Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Sample
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select '0F00', '240' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep' 

Select * FROM @Sample
ORDER By 
case 
when CHARINDEX(' ',titlename)-1 < 0 then LEFT(titlename,0) 
else LEFT(titlename,CHARINDEX(' ',titlename)-1) 
end,
tvd_type DESC,
Case 
WHEN @SortOrder = 'A-z' AND tvd_type = 'ep' THEN title_id 
WHEN @SortOrder = 'A-z' THEN TitleName 
END


Declare @Output Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Output
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , ' FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , ' 210B3938','DF316998','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , ' 210B3938','AC37AEC9','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , ' 210B3938','34CD2C1B','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , ' 210B3938','477FA861','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , ' 210B3938','D45455B2','Ep' 

Select * From @Output

No, you're never too old to Yak'n'Roll if you're too young to die.

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 09:44:06

Hi,
Thanks for u r Effort.

But Sort Order Fails. please chk with Full Data .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 10:05:55

Can I play too?

SELECT		*
FROM		@Sample
ORDER BY	REPLACE(TitleName, ' ', CHAR(255))

E 12°55'05.63"
N 56°04'39.26"

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 10:13:23

Hi peso,

Sort order Fails.please consider this

if Tvd_Type = 'Ep' then it should be sort on Title_Id.

please chk the above @output Table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 10:24:25

[code];WITH Yak (Title_Object_Id, [Path])
AS (
SELECT Title_Object_Id,
'/' + CAST(ROW_NUMBER() OVER (ORDER BY TitleName) AS VARCHAR(MAX)) + '/'
FROM @Sample
WHERE Tvd_Type = 'Ser'

UNION ALL

SELECT s.Title_Object_Id,
CASE
WHEN s.Tvd_Type = 'Ep' THEN y.[Path] + CAST(ROW_NUMBER() OVER (ORDER BY s.Title_Id) AS VARCHAR(MAX)) + '/'
ELSE y.[Path] + CAST(ROW_NUMBER() OVER (ORDER BY s.TitleName) AS VARCHAR(MAX)) + '/'
END
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Parent_Object_Id = y.Title_Object_Id
)

SELECT s.Title_Id,
s.TitleName,
s.Parent_Object_Id,
s.Title_Object_Id,
s.Tvd_Type
FROM Yak AS y
INNER JOIN @Sample AS s ON s.Title_Object_Id = y.Title_Object_Id
ORDER BY y.[Path][/code]

E 12°55'05.63"
N 56°04'39.26"

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 10:28:52

Hi,

Sort Order Still Fails.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 10:35:23

This too?

;WITH Yak (Title_Object_Id, [Path])
AS (
	SELECT	Title_Object_Id,
		'/' + CAST(STR(ROW_NUMBER() OVER (ORDER BY TitleName), 12) AS VARCHAR(MAX)) + '/'
	FROM	@Sample
	WHERE	Tvd_Type = 'Ser'

	UNION ALL

	SELECT		s.Title_Object_Id,
			CASE
				WHEN s.Tvd_Type = 'Ep' THEN y.[Path] + STR(ROW_NUMBER() OVER (ORDER BY s.Title_Id), 12) + '/'
				ELSE y.[Path] + STR(ROW_NUMBER() OVER (ORDER BY REPLACE(s.TitleName, ' ', CHAR(255))), 12) + '/'
			END
	FROM		Yak AS y
	INNER JOIN	@Sample AS s ON s.Parent_Object_Id = y.Title_Object_Id
)

SELECT		s.Title_Id,
		s.TitleName,
		s.Parent_Object_Id,
		s.Title_Object_Id,
		s.Tvd_Type
FROM		Yak AS y
INNER JOIN	@Sample AS s ON s.Title_Object_Id = y.Title_Object_Id
ORDER BY	y.[Path]

E 12°55'05.63"
N 56°04'39.26"

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-17 : 10:40:13

Hi,

Some Records are missing .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 13:41:35

Which records?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-17 : 14:32:28

There are 35 records in the sample data, and there are 35 records in the output.
What more do you expect?

Declare @Sample Table
(
Title_Id Varchar(100),
TitleName Varchar(128),
Parent_Object_Id Varchar(128),
Title_Object_Id Varchar(128),
Tvd_Type VArchar(128)

)
InSert Into @Sample
Select 'N000', '101 CARS ' ,'24C5F2D0','3110235B', 'Ser' Union All
Select 'N100', '101 CARS - 01 ' ,'3110235B','42804FA1','Sea ' Union All
Select 'N101', '101 CARS - 01 - BIRD ' ,'42804FA1','2160FEFC','Ep' Union All
Select 'N102', '101 CARS - 01 - I JUST' ,'42804FA1','187E41E6','Ep' Union All
Select 'N103', '101 CARS - 01 - A LOT ' ,'42804FA1','C24A58A8','Ep' Union All
Select 'N104', '101 CARS - 01 - KEEPING IT' ,'42804FA1' ,'EEAA05D1','Ep' Union All
Select 'N105', '101 CARS - 01 - EP #5 ' ,'42804FA1','43A6D843','Ep' Union All
Select 'N106', '101 CARS - 01 - EP #6 ' ,'42804FA1','471FA93B','Ep' Union All
Select 'N107', '101 CARS - 01 - EP #7 ' ,'42804FA1','50636951','Ep' Union All
Select 'N108', '101 CARS - 01 - EP #8 ' ,'42804FA1','2E49B01B','Ep' Union All
Select 'N109', '101 CARS - 01 - EP #9 ' ,'42804FA1','334E39D0','Ep' Union All
Select 'N110', '101 CARS - 01 - EP #10 ' ,'42804FA1','06016B84','Ep' Union All
Select '0F00', '240 ' , ' 24C5F2D0','FA74740C','Ser' Union All
Select '1F00', '240 - 01 ' , 'FA74740C','210B3938','Sea ' Union All
Select '1F01', '240 - 01 - 1:00 A.M. - 2:00 A.M. ' , '210B3938','DF316998','Ep' Union All
Select '1F10', '240 - 01 - 10:00 A.M. - 11:00 A.M. ' , '210B3938','AC37AEC9','Ep' Union All
Select '1F11', '240 - 01 - 11:00 A.M. - 12:00 P.M. ' , '210B3938','34CD2C1B','Ep' Union All
Select '1F13', '240 - 01 - 1:00 P.M. - 2:00 P.M. ' , '210B3938','477FA861','Ep' Union All
Select '1F22', '240 - 01 - 10:00 P.M. - 11:00 P.M. ' , '210B3938','D45455B2','Ep' Union All
Select '2F00', '24 - 02' , 'FA74740C', 'E6A72981', 'Sea' Union All 
Select '2F03', '24 - 02 - 10:00 A.M. - 11:00 A.M.' , 'E6A72981', '9DBD0274', 'Ep' Union All
Select '2F04', '24 - 02 - 11:00 A.M. - 12:00 P.M.' , 'E6A72981', 'D102F5A8', 'Ep' Union All
Select '2F06', '24 - 02 - 1:00 P.M. - 2:00 P.M.' , 'E6A72981', 'C90041EA', 'Ep' Union All
Select '2F15', '24 - 02 - 10:00 P.M. - 11:00 P.M.' , 'E6A72981', '1D1A66CC', 'Ep' Union All
Select '2F16', '24 - 02 - 11:00 P.M. - 12:00 A.M.' , 'E6A72981', '24FCCE23', 'Ep' Union All
Select '2F17', '24 - 02 - 12:00 A.M. - 1:00 A.M.' , 'E6A72981', 'A8279615', 'Ep' Union All
Select '2F18', '24 - 02 - 1:00 A.M. - 2:00 A.M.' , 'E6A72981', 'EBE31EC6', 'Ep' Union All
Select '3F00', '24 - 03', 'FA74740C', 'BCECCC49', 'Sea' Union All 
Select '3F01', '24 - 03 - 1:00 P.M. - 2:00 P.M.' , 'BCECCC49', '65756B84', 'Ep' Union All
Select '3F10', '24 - 03 - 10:00 P.M. - 11:00 P.M.' , 'BCECCC49', 'D4914743', 'Ep' Union All
Select '3F11', '24 - 03 - 11:00 P.M. - 12:00 A.M.' , 'BCECCC49', 'E0893040', 'Ep' Union All
Select '3F12', '24 - 03 - 12:00 A.M. - 1:00 A.M.' , 'BCECCC49', 'AE0D8BE3', 'Ep' Union all
Select '3F13', '24 - 03 - 1:00 A.M. - 2:00 A.M.' , 'BCECCC49', 'C71D3DA3', 'Ep' Union All
Select '3F22', '24 - 03 - 10:00 A.M. - 11:00 A.M.' , 'BCECCC49', '060D9289', 'Ep' Union All
Select '3F23', '24 - 03 - 11:00 A.M. - 12:00 P.M.' , 'BCECCC49', '525633B7', 'Ep'

;WITH Yak (Title_Object_Id, [Path])
AS (
	SELECT	Title_Object_Id,
		'/' + CAST(STR(ROW_NUMBER() OVER (ORDER BY TitleName), 12) AS VARCHAR(MAX)) + '/'
	FROM	@Sample
	WHERE	Tvd_Type = 'Ser'

	UNION ALL

	SELECT		s.Title_Object_Id,
			CASE
				WHEN s.Tvd_Type = 'Ep' THEN y.[Path] + STR(ROW_NUMBER() OVER (ORDER BY s.Title_Id), 12) + '/'
				ELSE y.[Path] + STR(ROW_NUMBER() OVER (ORDER BY REPLACE(s.TitleName, ' ', CHAR(255))), 12) + '/'
			END
	FROM		Yak AS y
	INNER JOIN	@Sample AS s ON s.Parent_Object_Id = y.Title_Object_Id
)

SELECT		s.Title_Id,
		s.TitleName,
		s.Parent_Object_Id,
		s.Title_Object_Id,
		s.Tvd_Type
FROM		Yak AS y
INNER JOIN	@Sample AS s ON s.Title_Object_Id = y.Title_Object_Id
ORDER BY	y.[Path]

E 12°55'05.63"
N 56°04'39.26"

ranganath
Posting Yak Master

209 Posts

Posted - 2009-06-18 : 02:08:30

Works great , thanks a lot

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources