Many threaded discussion groups create a thread_ID for the first topic in any thread. That thread_ID is usually stored invisibly with each response. Each response also maintains (invisibly, too) the message id of it's parent. Of course the parent_ID of the root message in any thread would be null.
I think I understand that you are archiving mailing list responses, that precludes the idea of knowing the parent message id at the time a new message is created. So, all you could do is to parse the responses and see if any of them match to a subject line without any "RE:", "FW:", "PR:", etc then associate them with an identified thread. The problem with this is in the timing. Using your sample data, you couldn't determine a thread_id for messages 1-3 until message 4 arrives. More complex parsing could help you to determine if more than one message is in the body (replies with history) and determine "parental lineage" from the content. I guess you could combine subject headers and message senders into some kind of key value for this purpose but you have to be careful to make the proper match. What you are doing is not as easy as running your own discussion group as you can't embed useful metadata into each message/response. Sorry I couldn't help more. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jochem van Dieten <[EMAIL PROTECTED]> wrote on 08/13/2004 06:58:07 AM: > On Wed, 11 Aug 2004 23:40:27 -0700, Scott Haneda <[EMAIL PROTECTED]> wrote: > > > > I made a mailing list archiver, I "thread" discussions by subject. I chose > > to not use message-id's since so many people hijack threads. > > Why not select/group on subject, and then thread on messageid. > > > > Given this case: > > > > Subject ID > > RE: Order by with one exception 1 > > RE: Order by with one exception 2 > > RE: Order by with one exception 3 > > Order by with one exception 4 > > RE: Order by with one exception 5 > > RE: Order by with one exception 6 > > RE: Order by with one exception 7 > > > > As you can see, these are in correct order, but in this case, I want to push > > the one without the Re: to the top. I can not just order by subject, id, > > since not a subject could start with a letter after "R". Suggestions? > > SELECT * > FROM table > ORDER BY subject NOT LIKE 're:%', ID > > Jochem > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
