question

vvk avatar image
vvk asked

Ordering

Given then table below: [1]: /storage/temp/2950-sqltable.jpg Is it possible to order alphabetically such that children of Elephant(id,location,name) are also sorted? The output should be bat,cat,dog,elephant,id,location,name,fox.
ordering
sqltable.jpg (15.7 KiB)
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
sabinweb avatar image
sabinweb answered
One way do to this , is to do a LEFT JOIN with the same table. This approach is not optimal, because you are scanning the same table twice. `Scan count 2` (But this is what I have in mind right now) DECLARE @sqlTable TABLE ( ID INT NOT NULL, [Type] VARCHAR(50) NOT NULL, [Level] TINYINT NOT NULL, [Parent] TINYINT NOT NULL) INSERT INTO @sqlTable ([ID],[Type],[Level],[Parent]) SELECT 1 AS [ID], 'Fox' AS [Type], 0 AS [Level], 0 AS [Parent] UNION ALL SELECT 2,'Dog',0,0 UNION ALL SELECT 3,'Cat',0,0 UNION ALL SELECT 4,'Elephant',0,0 UNION ALL SELECT 5,'Location',1,4 UNION ALL SELECT 6,'Name',1,4 UNION ALL SELECT 7,'Id',1,4 SELECT A.[ID] ,A.[Type] ,A.[Level] ,A.[Parent] FROM @sqlTable AS A LEFT JOIN @sqlTable AS B ON A.Parent = B.ID ORDER BY COALESCE(B.[Type],A.[Type]) ,A.[Level] ,A.[Type] --added lated --Scan count 2 With the output: ID Type Level Parent 3 Cat 0 0 2 Dog 0 0 4 Elephant 0 0 7 Id 1 4 5 Location 1 4 6 Name 1 4 1 Fox 0 0
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vvk avatar image vvk commented ·
This is still an issue. The children of Elephant (Location, Name, Id) are not sorted. Will adding another column while creating the children help?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.