Re: Getting a grip on nested intervals and matrix encoding

On Sep 6, 9:42 am, creecode <creec..._at_gmail.com> wrote:
> 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
> 14677   2283    1523    1523    1016    1.1.254.1.1     1.499013806     1.499015101     4
> 14990138058.501
>
> Any thoughts on either what I might have done wrong or a query that
> will produce unique order position values?
>
> Toodle-looooooooo............
> creecode- Hide quoted text -
>
> - Show quoted text -

select name, (a11-a12)/(a21-a22)*10000000000-a11/a21 pos from hier;

1.1.84.23            14970398968.901930438437591866304249136
1.1.84.23.1          14970398968.9019304695082500851489389089