From: | Ivan Polak <ivan(dot)polak(at)f4s(dot)sk> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | LTREE extension and "order by" |
Date: | 2011-07-20 21:37:02 |
Message-ID: | CAN=kwkvn62omh-hQ4brmynuUMXmAH3NVx3SV-aLjJqJHa2TdSA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | pasman pasmański | 2011-07-21 01:44:29 | Re: LTREE extension and "order by" |
Previous Message | Kevin Crain | 2011-07-20 14:39:55 | Re: compile postgres with visual studio 2010 |