RE: Threaded Records in SQL: Advice Needed

Hello,

I don't know if it has been answered yet (bit late I know).
I solved the ascii numeric ordering as followed:
- concatenate a lot of zeroes in front (at least the length of the result
value)
- cut the the result length.


snlsor=> select * from asciinum order by number;
number
------    1   10  111 1211    2   20  211   22
(8 rows)
snlsor=> select * from asciinum order by lpad(number, 4, '0');
number
------    1    2   10   20   22  111  211 1211
(8 rows)

Best regards,
Roelof
> -----Original Message-----
> From:    Ingram, Bryan [SMTP:BIngram@sixtyfootspider.com]
> Sent:    dinsdag 11 april 2000 22:58
> To:    Ingram, Bryan; mig@utdt.edu
> Cc:    pgsql-sql@postgresql.org
> Subject:    RE: [SQL] Threaded Records in SQL: Advice Needed
> 
> In reference to the ascii/numeric ordering problem ..
> 
> But first let me say ..if someone knows of a way to get ascii values to
> order as if they were numerics ..please let me in on the secret ..
> 
> Instead of using numbers, if letters were used the ordering would be
> correct.
> 
> The id string would become something like: A/A/A for the 1st reply to the
> 1st reply of the 1st root message.
> 
> This will work fine for threads with relatively few replies on the same
> level.  For instance, the number 100 would take only 4 characters to
> encode,
> however, the number 1000 would need 39 characters to encode.
> 
> For my purpose 100-200 replies on any given level will suffice nicely.
> 
> Even though text fields are limited to 4096 characters, even if each level
> needed 40 characters for encoding, I would still have room for
> approximately
> 100 levels (plenty) ..
> 
> Adding a step to the procedure you developed, we could convert the
> returned
> "next reply number" into its alphabetic equivalent.
> 
> e.g.  1 =  A
>     2 =  B
>     26 = Z
>     27 = ZA
>     28 = ZB
>     52 = ZZ
>     53 = ZZA
> 
> The procedure is this:
> 
> next_id/26 = number of Z's
> mod of next_id/26 = numeric position within alphabet
> 
> So .. if you wanted to add the 100th reply to the 2nd root topic ...
> 
> 100/26         = 3 (ZZZ) (drop everything but the integer)
> mod of 100/26     = 12 (L)
> 
> So we'd have 3 Z's and an L for the second root topic.
> 
> The fully assembled ID would be /B/ZZZL
> 
> Then a reply to this would become /B/ZZZL/A
> 
> So on, and so forth ..
> 
> This is verging on getting kludgey, but it still looks like it meets all
> of
> the criteria:
> 
> 1) Fast inserts
> 2) Fast selects of a thread or part of a thread
>    Actually this criterion hinges on the quality of the indexing
> 3) Rows are returned in order, with only one select.
> 
> So ..I really wish there was a better way ..but this isn't too big of a
> price to pay.
> 
> Thanks,
> Bryan
> 
> 
> 
> 
> 
> 
> -----Original Message-----
> From: Ingram, Bryan 
> Sent: Tuesday, April 11, 2000 3:05 PM
> To: 'mig@utdt.edu'
> Cc: pgsql-sql@postgresql.org
> Subject: RE: [SQL] Threaded Records in SQL: Advice Needed
> 
> 
> Thanks for the ideas on the functions, that'll work nicely.
> 
> The only other problem I see in actually implementing this, is that the id
> column i.e. /25/10/2/ will not be ordered correctly because it relies on
> ascii values.  You get alphabetic orderings, rather than numerical.
> 
> Such as:
> 
> 1
> 10
> 11
> 12
> 14
> 2
> 20
> 25
> 3
> 4
> 5
> 
> instead of
> 
> 1
> 2
> 3
> 4
> 5
> 10
> 11
> 12
> 14
> 20
> 25
> 
> Any ideas how to get around this?  I'm working on the problem right now,
> but
> haven't found anything yet.
> 
> Bryan
>