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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-15 : 23:39:01
|
| Max writes "Here's a tough one.I have a recursive join pulling the parent and children threads in a message board. Each thread has its own record in the table Msg. How could you order the parent threads by the date of the children threads?Thanks-" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-16 : 07:58:52
|
| Without the data structures you're using, it'll be hard to say whether you can do this or not, but I'll take a stab at it.When you say "recursive join", I'm assuming that you're getting all the parent threads and putting them into a temp or working table of some kind, then getting the child threads, grandchildren etc. and putting them into the working table too. I'm also assuming this is NOT being done in Oracle using CONNECT BY PRIOR (if you ARE using Oracle, well, we specialize in SQL Server, and I don't think you can do it anyway with CONNECT BY)Take a look at graz's articles on threaded discussions:http://www.sqlteam.com/item.asp?ItemID=1353http://www.sqlteam.com/item.asp?ItemID=1602In his structure, the topmost thread is always indicated, so sorting within a thread is no problem. You can also use a subquery to find the most recent post for each thread, then JOIN in to the main query, and that will order the parent threads the way you want (I think):SELECT T.* FROM threads T INNER JOIN (SELECT threadID, Max(PostDate) MaxDate FROM threads GROUP BY threadID) MON T.threadID=M.threadIDORDER BY M.MaxDate DESC |
 |
|
|
|
|
|
|
|
Using a SELECT in ORDER BY