SQL Server Ordering a self-referencing table grouped by parent/children by date

6tdlim6h  于 2023-04-28  发布在  其他
关注(0)|答案(2)|浏览(175)

I'v got a self-referencing table like this, where the parent_id is a foreign key on id :

+----+-----------+------------+
| id | parent_id | date       |
+----+-----------+------------+
| 1  | NULL      | 2023-04-10 |
| 2  | 4         | 2023-04-13 |
| 3  | 7         | 2023-05-20 |
| 4  | NULL      | 2023-04-13 |
| 5  | 1         | 2023-05-20 |
| 6  | 7         | 2023-03-14 |
| 7  | NULL      | 2023-04-15 |
| 8  | 1         | 2023-03-18 |
| 9  | 1         | 2023-04-19 |
+----+-----------+------------+

And I want to print the data in a form like this, grouped by parent/children and ordered by date:

+----+-----------+------------+
| id | parent_id | date       |
+----+-----------+------------+
| 7  | NULL      | 2023-04-15 |<--
| 3  | 7         | 2023-05-20 |
| 6  | 7         | 2023-03-14 |
| 4  | NULL      | 2023-04-13 |<--
| 2  | 4         | 2023-02-20 |
| 1  | NULL      | 2023-04-10 |<--
| 5  | 1         | 2023-05-20 |
| 9  | 1         | 2023-04-19 |
| 8  | 1         | 2023-03-18 |
+----+-----------+------------+

Is there a way to order the parents by date, but keep the group with their children? To group the parents with their children, the COALESCE -Operator suggested in this post Order by in a self-referencing table might work, but can't figure out how to additionally order by date. Something like this doesn't work:
SELECT * FROM my_table ORDER BY COALESCE(parent_id, id), date

Any ideas? Preferably without expensive joins...

Databases are H2 on dev environment and MS SQL in production, JPA/Hibernate as data access layer.

waxmsbnn

waxmsbnn1#

For the given sample data of only two levels, we can use a simple window agg in ORDER BY to find the max date for any parent and apply it to its children, then order by that first. Then we order by parent_id , which makes sure the parent sorts first, then order by date DESC for the children (if necessary).

ORDER BY MAX(CASE WHEN parent_id IS NULL THEN date END) 
  OVER (PARTITION BY COALESCE(parent_id, id)) DESC,
  parent_id, date DESC;
vaj7vani

vaj7vani2#

Assuming your hierarchy is more than two levels... otherwise Aaron's approach would be the way to go.

This is a small matter using a recursive CTE with the window function row_number() over() to construct a sort string.

with cteH
 as (
     Select ID
           ,Parent_ID
           ,Level=1
           ,Date 
           ,Srt  = convert(varchar(500),10000+row_number() over (order by date desc))
      From  YourTable where Parent_ID is null
     Union All
     Select h.ID
           ,h.Parent_ID
           ,cteH.Level+1
           ,h.Date 
           ,Srt  = convert(varchar(500),concat(cteH.Srt,'|',10000+row_number() over (order by h.date desc) ))
      From  YourTable h Join cteH ON h.Parent_ID = cteH.ID
    )
Select *
 From cteH
 Order By Srt

Results

Note that I used 10000+row_number() This is to avoid alpha sort 1,10,11,2,3 vs the desired 1,2,3,...10,11

相关问题