Hi, in postgreSQL (with LTREE extension) database I have the following table "comments":
id BIGINT /* id */ article_id BIGINT /*article-id */ parent_id BIGINT comment TEXT path LTREE level INTEGER /* level */ with the following rows: id article_id comment parent_id path level 1 1 aaaa 1 1 2 1 bbbb 1 1.2 2 3 1 cccc 2 1.2.3 3 4 1 dddd 2 1.4 2 5 1 eeee 4 1.4.5 3 6 1 ffff 6 1 7 1 gggg 6 6.7 2 8 1 hhhh 6 6.8 2 9 1 iiii 9 1 10 1 jjjj 10 1 11 1 kkkk 5 1.4.5.11 4 and I need to select complete tree (with correct order of comments). SELECT * from comments where article_id = 2 order by <???> when I used: SELECT * from comments where article_id = 2 order by path the result is: id comment path 1 aaaa 1 2 bbbb 1.2 3 cccc 1.2.3 4 dddd 1.4 5 eeee 1.4.5 11 kkkk 1.4.5.11 10 jjjj 10 6 ffff 6 7 gggg 6.7 8 hhhh 6.8 9 iiii 9 BUT, it is wrong, because comment with id = 10 is after comment with id=11 (i know, this is correct, because ordering by column PATH [as TEXT], and 10 is 'after' 1.4.5.11) , but I need : id comment path 1 aaaa 1 2 bbbb 1.2 3 cccc 1.2.3 4 dddd 1.4 5 eeee 1.4.5 11 kkkk 1.4.5.11 6 ffff 6 7 gggg 6.7 8 hhhh 6.8 9 iiii 9 10 jjjj 10 thanks Ivan -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
