Order By Sql Query Based On Value Another Column

I have table like this: And I want to short the data based on ParentID that refer to ID. So, if the data with ParentID 5 it will be under the data with ID 5. So this is the expect

Solution 1:

You can use a common table expression (CTE) to first query the parent records, and then query the children records in the second query. If you create a sort column, you can then order the data perfectly:

createtable #temp (
  id int,
  name varchar(10),
  [date] date,
  parentid int
)

insertinto #temp values
(1, 'AAA', '9/7/2020', 1),
(2, 'BBB', '9/8/2020', 2),
(3, 'CCC', '9/8/2020', 3),
(4, 'DDD', '9/8/2020', 4),
(5, 'EEE', '9/8/2020', 2),
(6, 'FFF', '9/8/2020', 1),
(7, 'GGG', '9/8/2020', 5),
(8, 'HHH', '9/8/2020', 3),
(9, 'III', '9/8/2020', 4),
(10, 'JJJ', '9/8/2020', 10)

;with cte (id, parentid, name, [date], sort) as
(
  /* query #1 : pull only parent records */select id, parentid, name, [date],
         cast(right('0000'+cast(row_number() over (orderby id) asvarchar(5)), 5) asvarchar(1024))
  from   #temp
  where  id = parentid                           /* pull just the parent records */unionall/* query #2 : add children records */select t.id, t.parentid, t.name, t.[date],
         cast(c.sort +right('0000'+cast(row_number() over (orderby t.id) asvarchar(5)), 5) asvarchar(1024))
  from   cte c                                   /* include data from 1st query */innerjoin #temp t on c.id = t.parentid /* only pull children of the parent records returned in query #1 */where  t.id <> t.parentid                      /* a record cannot be a child of itself, prevents infinite recursion */
)
select*from   cte
orderby sort

droptable #temp

Which returns this dataset:

idparentidnamedatesort----------------------------------------------------------11AAA2020-09-07 0000161FFF2020-09-08 000010000122BBB2020-09-08 0000252EEE2020-09-08 000020000175GGG2020-09-08 00002000010000133CCC2020-09-08 0000383HHH2020-09-08 000030000144DDD2020-09-08 0000494III2020-09-08 00004000011010JJJ2020-09-08 00005

The key is the sort column, which builds a value that can be sorted alphabetically. Of course, you can exclude the sort column from the final output, but I wanted you to see how it builds the sort data.

The recursive nature of CTE queries means that it will continue to loop and re-run the sub-query until all the children + grandchildren + great-grandchildren, etc., have been pulled. That is why the sort data for the GGG record has 3 parts of data.

Solution 2:

You're likely looking to sort all of the data hierarchically. This can be best done with CTE's

In your case the SQL would look like this:

with t as 
(select1 id, 'aaa' name, cast('20200907'asdate) date, 1 parentid unionselect2 id, 'bbb' name,  cast('20200908'asdate) date, 2 parentid unionselect3 id, 'ccc' name,  cast('20200909'asdate) date, 3 parentid unionselect4 id, 'ddd' name,  cast('20200910'asdate) date, 4 parentid unionselect5 id, 'eee' name,  cast('20200911'asdate) date, 2 parentid unionselect6 id, 'fff' name,  cast('20200912'asdate) date, 1 parentid unionselect7 id, 'ggg' name,  cast('20200913'asdate) date, 5 parentid unionselect8 id, 'hhh' name,  cast('20200914'asdate) date, 3 parentid unionselect9 id, 'iii' name,  cast('20200915'asdate) date, 4 parentid unionselect10 id, 'jjj' name, cast('20200916'asdate) date, 10 parentid)

, t1 (id, name, date, parentid, level) as (
select id, name, date, parentid, 0from t where id = parentid  --starting condition (top level, no higher ancestors)unionallselect t.id, t.name, t.date, t.parentid, t1.level +1--level goes downfrom t join t1 on t.parentid = t1.id --hierarchy condition, add those elements that have a direct ancestor already selectedand t.id != t1.parentid --cannot reselect them, otherwise the recursion won't stop
)

select*from t1 orderby level, id

The end result looks like this

Solution 3:

Please try using self join like this:

SELECT t1.*, t2.DateFROM TableName t1
INNER JOIN TableName t2 ON t2.ID = t1.ParentId

Solution 4:

On a simple dataset that is a hierarchy with a finite number of levels that is small, you can do this by simply self joining.

You could use this method instead of a CTE if your database engine does not support CTEs or if you needed to keep the query maintenance simple.

Setup using table variable

DECLARE@tabletable (
  id int,
  name varchar(10),
  [date] date,
  parentid int
)

insertinto@tablevalues
(1, 'AAA', '9/7/2020', 1),
(2, 'BBB', '9/8/2020', 2),
(3, 'CCC', '9/8/2020', 3),
(4, 'DDD', '9/8/2020', 4),
(5, 'EEE', '9/8/2020', 2),
(6, 'FFF', '9/8/2020', 1),
(7, 'GGG', '9/8/2020', 5),
(8, 'HHH', '9/8/2020', 3),
(9, 'III', '9/8/2020', 4),
(10, 'JJJ', '9/8/2020', 10)

Query from the @table with 3 levels of depth

SELECT child.id, child.name, child.[date], child.parentId
FROM@table child
LEFTOUTERJOIN@table parent on child.parentid = parent.id
ORDERBY parent.parentid, child.parentid, child.id

If your data structure has nulls to indicate that there is no parent, then this query can be more efficient again, but we have to COALESCE the Ids:

SELECT child.id, child.name, child.[date], child.parentId
FROM@table child
LEFTOUTERJOIN@table parent on child.parentid = parent.id
orderbyCOALESCE(parent.parentid,child.parentid,child.Id), IsNull(child.parentid,child.Id), child.id

Finally, to support each additional level of recursion, add another join, ultimately you will need n-2 joins where n is the maximum number of levels your data will support, the following supports 4 levels:

SELECT child.id, child.name, child.[date], child.parentId
FROM@table child
LEFTOUTERJOIN@table parent on child.parentid = parent.id
LEFTOUTERJOIN@table grandparent on parent.parentid = grandparent.id
orderbyCOALESCE(grandparent.parentId, parent.parentid,child.parentid,child.Id), COALESCE(parent.parentid,child.parentid,child.Id), IsNull(child.parentid,child.Id), child.id