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(at)sixtyfootspider(dot)com]
> Sent: dinsdag 11 april 2000 22:58
> To: Ingram, Bryan; mig(at)utdt(dot)edu
> Cc: pgsql-sql(at)postgresql(dot)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(at)utdt(dot)edu'
> Cc: pgsql-sql(at)postgresql(dot)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
>