Timeline for How do I sort the results of a recursive query in an expanded tree-like fashion?
Current License: CC BY-SA 4.0
13 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 9, 2022 at 17:39 | comment | added | Erwin Brandstetter |
@AndyFusniak: I haven't worked with KSUIDs, yet. Sounds like a smart approach. From a storage perspective char(27) seems like a poor choice to store 20 bytes of information, though. Related: dba.stackexchange.com/a/115316/3684, dba.stackexchange.com/a/256817/3684
|
|
| Feb 9, 2022 at 17:08 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
focus
|
| Feb 9, 2022 at 16:29 | comment | added | Andy Fusniak | @ErwinBrandstetter Fantastic! I'm glad you mentioned the 2nd query. I was stuck on that yesterday evening wondering if that was intended. On a separate note, I tried using a "K-Sortable Unique IDentifier" (ksuid) for the primary key as a CHAR(27) field. This appears to remove the need for the additional ORDER BY ts. This seems to provide a tree ordered by primary key provided each node in the tree isn't added within the same 1 second window (ksuids store the time part to a 1 second resolution). Would a CHAR(27) PK be less efficient that using the SEARCH ... BY ts ? | |
| Feb 9, 2022 at 14:17 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
denoise
|
| Feb 9, 2022 at 5:08 | comment | added | Erwin Brandstetter | @Andy: I added new syntax for Postgres 14. While being at it, I also fixed the buggy 2nd query. | |
| Feb 9, 2022 at 5:07 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
fix bug in second query; add new syntax for pg 14 per question from Andy; add fiddle
|
| Feb 8, 2022 at 16:36 | comment | added | Andy Fusniak | @ErwinBrandstetter I came across this post whilst seeking a solution for time ordered nested comments. I notice Postgres has “SEARCH DEPTH FIRST BY id SET ordercol” which appears to be a new syntax feature added since this answer. Would you be able to add an example using this new syntax? | |
| Apr 14, 2014 at 15:11 | comment | added | Erwin Brandstetter | @JohnCand: You can shift the path by one towards the root (repeat the root node in first position!) and order by that column additionally ... | |
| Apr 14, 2014 at 15:05 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
add solution
|
| Apr 14, 2014 at 14:27 | comment | added | JohnCand | Thank you, it works great! However, what about the "If two or more child nodes have the same parent node, I want them to be sorted by their timestamp" part? Is this doable with this approach? It might not always be the case that a higher node ID corresponds to a later time. | |
| Apr 14, 2014 at 14:27 | vote | accept | JohnCand | ||
| Apr 14, 2014 at 14:04 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clean up
|
| Apr 14, 2014 at 13:57 | history | answered | Erwin Brandstetter | CC BY-SA 3.0 |