0

I am trying to find the parent with the most children. I feel like this should not be so hard however I find myself struggling.

I am using postgresql and Ltree.

Thank you

Davinj
  • 327
  • 4
  • 16

1 Answers1

2

It's the same logic as for any other case, find a function that will give you the parent and group by it. This should work:

SELECT subpath(path,0,1), count(*) 
FROM test
GROUP BY subpath(path,0,1)
ORDER BY count(*) DESC limit 1;

http://sqlfiddle.com/#!12/8fb17/5

Jakub Kania
  • 15,665
  • 2
  • 37
  • 47
  • Thank you for your response. I get a PG::Error: ERROR: invalid positions – Davinj Mar 24 '13 at 08:46
  • @Davinj The example fiddle works, I can't really say why you're getting the error without seeing your query and schema. – Jakub Kania Mar 24 '13 at 08:53
  • @Davinj Oh, I see what causes the error, you'll need a condition in WHERE clause to limit the select only to those ltrees that have enough levels. See here: http://sqlfiddle.com/#!12/8fb17/15 – Jakub Kania Mar 24 '13 at 08:58
  • Gotcha! yes when I got rid of the "" (empty) paths it worked great. I appreciate your quick response. Thank you! – Davinj Mar 24 '13 at 09:07
  • In my tree structure I have a parent that has 6 children and this query is giving me a node that has 1 child instead of the parent that has 6 – Davinj Mar 24 '13 at 09:35
  • @Davinj It should work, I can't really say what's wrong without seeing the query and example of the behaviour. Maybe you ommited the DESC in order by? Remove the limit and see if the count is correct. – Jakub Kania Mar 24 '13 at 10:07
  • 1
    @JakubKania: Should have been `subpath(path, -1, 1)`. [More details in the answer to his follow-up](http://stackoverflow.com/questions/15601829/postgresql-ltree-query-to-find-parent-with-most-children-excluding-root). – Erwin Brandstetter Mar 24 '13 at 23:11
  • @ErwinBrandstetter Thanks, I haven't thought about it that way. – Jakub Kania Mar 25 '13 at 09:07
  • Thank you for your response and I am sorry for the ambiguity. I re-asked the question at http://stackoverflow.com/questions/15601829/postgresql-ltree-query-to-find-parent-with-most-children-excluding-root – Davinj Mar 26 '13 at 20:51