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.
2条答案
按热度按时间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 byparent_id
, which makes sure the parent sorts first, then order by dateDESC
for the children (if necessary).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.Results
Note that I used
10000+row_number()
This is to avoid alpha sort1,10,11,2,3
vs the desired1,2,3,...10,11