Hello Vadim,
On Aug 16, 12:52 pm, Vadim Tropashko <vadim..._at_gmail.com> wrote:
> The records ordered by idx_left, idx_right desc. I assume you want a
> query that when given a node in a hioerarchy fetches the next node
> according to the ordering. I found that this query is a mess due to
> composite ordering key. Therefore, it makes sence to order the values
> by a scalar, something like (a11-a12)/(a21-a22)*10000000000 - a11/a21
>
> Here is the full query:
>
> with OrderedHier as ( select name, (a11-a12)/(a21-a22)*10000000000 -
> a11/a21 pos from hierarchy )
> SELECT h.name FROM OrderedHier h, OrderedHier node
> where node.name = 'BLAKE'
> and h.pos > node.pos
> and not exists (
> SELECT * FROM OrderedHier hh
> where h.pos > hh.pos
> and hh.pos > node.pos
> )
> ;
>
> fetching ALLEN. (Tested everything between KING and BLAKE)
I've run into a snag. It seems the query doesn't work for a larger
data set. There are duplicate values for some of the order
positions. Here is an example...
message_id a11 a12 a21 a22 materialized_path left right level
order_position
6926 6069 253 4054 169 1.1.84.23 1.497039897 1.49703996 3
14970398968.503
7150 11885 6069 7939 4054 1.1.84.23.1 1.497039897 1.497039929 4
14970398968.503
6917 3572 325 2385 217 1.1.108.10 1.497693726 1.49769392 3
14976937258.5023
7244 6819 3572 4553 2385 1.1.108.10.1 1.497693726 1.497693828 4
14976937258.5023
7873 10066 6819 6721 4553 1.1.108.10.1.1 1.497693726 1.497693795 5
14976937258.5023
11301 1692 565 1129 377 1.1.188.2 1.498670212 1.49867139 3
14986702118.5013
11307 2819 1692 1881 1129 1.1.188.2.1 1.498670212 1.498670919 4
14986702118.5013
14655 1523 763 1016 509 1.1.254.1 1.499013806 1.499015748 3
14990138058.501
