SQL for threaded discussions #2
Does this not break when a single hash value in the postkey jumps over 10?
You are doing an ORDER BY on a varchar column with data that looks like this:
That's fine - no problems, until you reach something like this:
Which will, I believe, be inserted in the wrong order - i.e. in front of this record:
This is clearly a problem if you want to allow more than 10 direct replies to each parent post. Here is a more thorough solution I developed more than a year ago. Change your postkey columns to look like this:
With the above setup, our 22.214.171.124 will look like this:
and will order correctly, falling behind
as it should.
You will notice that this allows up to 1,000 direct replies under each parent. Every zero you add to each level will increase the number of possible direct replies by 10.
In ASP I use a config file to define the number of digits I want in each level. That way, if I want to allow 1 million direct replies to any parent, I set the digits param to 6 (000001 - 999999).