Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: SQL for Threaded Discussion (Part II)
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 12/06/2000 :  21:32:29  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
webguru22 writes "Hi SQL gurus, Here's a twist on the old "threaded message board query" question: I need to come up with a data model that will let me have elements in an "outline" with numbered sub-elements." Actually a paraphrased his question quite a bit. You can see the original question in the forums though.

Article Link.

Starting Member

0 Posts

Posted - 01/31/2001 :  09:44:22  Show Profile  Reply with Quote
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 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).


Go to Top of Page

Starting Member

10 Posts

Posted - 12/30/2001 :  20:44:57  Show Profile  Reply with Quote

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:

If I understand the code correctly, the recordset that displays the threaded records is from the newly created temp table:

Create Table #NestedPosts (
SortID int IDENTITY (1,1),
PostID int,
PostKey varchar(200),
PostLevel int )

The sp then appends records to this temp table one at a time in the threaded order. The temp table has SortID, which is an IDENTITY column, so it will automatically number these records in the order they are placed.

The client code would then use SortID as the OrderBy column, so the scheme you described is for NUMBERING only, not for the OrderBy.

(Of course, I could be wrong.)

Go to Top of Page

Starting Member

2 Posts

Posted - 05/17/2009 :  20:19:02  Show Profile  Reply with Quote
How to translate this into MYSQL? (Just learning stored procedures in MYSQL 5.0; have yet to find a good reference guide.)

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000