Threaded discussion revisited - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Threaded discussion revisited

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-05 : 20:51:47

Xstream writes "I'm working on setting up a threaded discussion forum, and I have the threads working properly except for one little thing. I would really like the threads to list with the newest topic first and then to oldest. I'm not sure how to do this, and still get the threads to list properly.

Here's what I mean:

topic 1----------5/4/02 - 2pm
child 1 topic 1 ----------- 5/5/02 - 1pm
topic 2----------3/5/02 - 5pm
child 1 topic 2 ----------- 5/5/02 - 1pm

Any ideas?
I used the examples from this article here: threaded article

my test is being done here: test

Xstream"

xstream
Starting Member

11 Posts

Posted - 2002-05-06 : 14:07:06

quote:

Xstream writes "I'm working on setting up a threaded discussion forum, and I have the threads working properly except for one little thing. I would really like the threads to list with the newest topic first and then to oldest. I'm not sure how to do this, and still get the threads to list properly.

Here's what I mean:

topic 1----------5/4/02 - 2pm
child 1 topic 1 ----------- 5/5/02 - 1pm
topic 2----------3/5/02 - 5pm
child 1 topic 2 ----------- 5/5/02 - 1pm

Any ideas?
I used the examples from this article here: <A href="http://www.sqlteam.com/item.asp?ItemID=1353">threaded article</A>

my test is being done here: <A href="http://www.piestactics.com/about/xstream/dev/">test</A>

Xstream"

Anyone?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-06 : 14:23:00

quote:
I would really like the threads to list with the newest topic first and then to oldest. I'm not sure how to do this, and still get the threads to list properly.

You could try a subquery that gets the maximum postdate for a parent thread, then ORDER BY it:

SELECT P.ThreadID, P.PostID, P.PostDate
FROM Posts P INNER JOIN
(SELECT ThreadID, Max(PostDate) MaxPost FROM Posts GROUP BY ThreadID) M
ON P.ThreadID=M.ThreadID
ORDER BY M.MaxPost DESC, P.ThreadID, P.PostDate DESC

xstream
Starting Member

11 Posts

Posted - 2002-05-06 : 15:01:52

Here's the statement I'm using now:

srtSQL ="SELECT Subject, PostID, ThreadID, ParentID, PostLevel, SortOrder, PostDate From dev_forum where ThreadID = ANY (Select threadid from dev_forum where postdate > '" & markit & "') Order by ThreadID, postdate, SortOrder

How would I rework this with the statement you gave? I'm using the markit variable to pass a date, so the statement knows which posts to display.

X

xstream
Starting Member

11 Posts

Posted - 2002-05-06 : 15:43:33

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'P'.

Getting this error. I tried redoing the statement. Guess it didn't work...

X

xstream
Starting Member

11 Posts

Posted - 2002-05-06 : 16:01:14

Ok, take a look at this:

srtSQL ="SELECT P.Subject, P.PostID, P.ThreadID, P.ParentID, P.PostLevel, P.SortOrder, P.PostDate From dev_forum P inner join (SELECT threadiD, max(postdate) maxpost from dev_forum group by threadid) M on P.ThreadID=M.THREADID where P.ThreadID = ANY (Select threadid from dev_forum where postdate > '" & markit & "') Order by P.ThreadID DESC, SORTORDER, M.MAXPOST, P.postdate DESC"

This statement is getting the results I want. I'm just not sure if it's being done the best way. Any suggestions?

I'm integrating this statement:
srtSQL ="SELECT Subject, PostID, ThreadID, ParentID, PostLevel, SortOrder, PostDate From dev_forum where ThreadID = ANY (Select threadid from dev_forum where postdate > '" & markit & "') Order by ThreadID, postdate, SortOrder

With this one:

SELECT P.ThreadID, P.PostID, P.PostDate
FROM Posts P INNER JOIN
(SELECT ThreadID, Max(PostDate) MaxPost FROM Posts GROUP BY ThreadID) M
ON P.ThreadID=M.ThreadID
ORDER BY M.MaxPost DESC, P.ThreadID, P.PostDate DESC

X

Edited by - xstream on 05/06/2002 16:05:00

xstream
Starting Member

11 Posts

Posted - 2002-05-07 : 11:46:49

*BUMP*

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 12:50:51

quote:
This statement is getting the results I want. I'm just not sure if it's being done the best way.

There is no "best" way to do anything, especially in programming.

If it gives you the results you want, then what's the problem? I can't think of a better way to do it (or else I would've posted it), and if you can't either, then stick with what you've got now. If it ain't broke, don't fix it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources