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.

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 ·
Yes, add please this for the `ORDER BY` clause: `ORDER BY COALESCE(B.[Type],A.[Type]) ,A.[Level] ,A.[Type]`
0 Likes 0 ·
Awesome! thanks a ton!
0 Likes 0 ·
Welcome!
0 Likes 0 ·

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.