threaded forum style ''order by''
-
Could someone help be select all my messages and sort them in the right order for display? The idea is to display a message in date order unless it's a child of another message. Thus:
-msg1
--msg2
---msg3
--msg4
--msg5
-msg6
--msg7
And so forth. I'm sure this isn't hard, I just can't wrap my brain around it. Order by topicid, (parent?), then date would be ideal, but I don't know how to do a conditional in the order by.
Many thanks,
Oblio
-
I think this will work. I am giving the data that I tested it with.
create table #messageTemp (PK int, parentPK int, message varchar(50), messageDatetime datetime)
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (2, 5, 'This is a child of #1 message', '01/01/05 11:15 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (3, 5, 'This is a child of #1 message', '01/01/05 11:05 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (5, null, 'This is a top message #1', '01/01/05 8:00 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (4, null, 'This is a top message #2', '01/01/05 09:00 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (12, 4, 'This is a child of #2 message', '01/01/05 03:10 PM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (6, 4, 'This is a child of #2 message', '01/01/05 01:08 PM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (7, 4, 'This is a child of #2 message', '01/01/05 04:09 PM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (10, null, 'This is a top message #4', '01/01/05 10:00 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (8, null, 'This is a top message #3', '01/01/05 10:00 AM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (9, 8, 'This is a child of #3 message', '01/01/05 10:09 PM')
insert into #messageTemp (PK, parentPK, message, messageDateTime)
values (11, 10, 'This is a child of #4 message', '01/01/05 06:09 PM')
select main.*
from #messageTemp main
left join #messageTemp link on main.parentPK = link.PK
order by coalesce(link.messageDateTime,main.messageDateTime), coalesce(link.PK, main.PK), main.messageDateTime
-
Here's another possible approach. It is very similar to Adrienne's.
CREATE TABLE #Messages( PK integer,
ConnectorKey integer,
Message varchar(50),
MessageDate datetime)
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 1, 1, 'First Message', '8/18/2005 4:53:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 2, 2, 'Second Message', '8/20/2005 4:00:00 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 3, 2, 'Second Message Addendum', '8/22/2005 4:53:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 4, 4, 'Third Message Sent before Second Message', '8/19/2005 4:53:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 5, 4, 'Third Message Addendum', '8/22/2005 4:53:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 5, 5, 'Fourth Message', '8/22/2005 4:53:52 PM')
SELECT Message, MessageDate
FROM #Messages
INNER JOIN( SELECT ConnectorKey, MIN( MessageDate) AS MinDate
FROM #Messages
GROUP BY ConnectorKey) Connector
ON( #Messages.ConnectorKey = Connector.ConnectorKey)
ORDER BY Connector.MinDate, Connector.ConnectorKey
DROP TABLE #Messages
I wasn't born stupid - I had to study.
-
Adrienne's doesn't seem to work with more than two tiers.
Farrell, I can't make yours work. I've never seen a subquery in a join. Is it missing a parenthesis?
-
What's the problem with farrell's solution???
Look up derived tables in BOLs. It's basically another select that you name and reuse as a table in the select without the hassle of creating temp tables.
-
Well, the first issue is syntactic - the last two inserts are dupes.
However, the other, real, issue is that it also can't handle a third tier. Try these inserts:
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 1, 1, 'Grandparent', '8/18/2005 4:53:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 2, 1, 'parent', '8/20/2005 4:00:00 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 3, 2, 'child', '8/22/2005 4:53:52 PM')
-
Seems to me like you need more of a recursive algorithmn... Are you planning to load all the message in one shot anyways?
-
Yes. It's actually a task-list for an intranet, but the end result will be more like a forum. I need to order all the comments & replies in the right order for display. It's becoming more difficult than it seems it should be. I was trying to work with some of Joe Celko's examples, but find that it seems overly complicated for what I was trying to do. Specifically, I'm happy keeping my parent-child table, and converting to a nested set didn't seem like it would have any advantages. After all, I'm just displaying, not manipulating the records.
-
Should be simple with the right design >>
Select threadInfo from dbo.Table order by ThreadStartDate, MessageDateInput
-
I think you may have not understood my ConnectorKey field. That is what makes this recursive. Plus, I put message 3 in before message two to show that mine should be able to handle mistakes if someone types something incorrectly in one of the varchar fields.
I wasn't born stupid - I had to study.
-
Except:
grandparent-1, 17-Jan-05
parent-1, 18-Jan-05
grandparent-2, 19-Jan-05
parent-1, 19-Jan-05
grandparent-3, 20-Jan-05
child-1, 20-Jan-05
parent-2, 20-Jan-05
The order really needs to be 'topic, child-of, date'. It's the middle one that kills me.
-
You're right, I don't understand it. But when I copy it, and put in those modified inserts (with three tiers), it doesn't display them in the expected order.
-
Try adding the following to my original query:
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 5, 4, 'Third Message 2nd Addendum', '8/22/2005 4:54:52 PM')
I think you will find it works with many children...
I wasn't born stupid - I had to study.
-
What do you get? I ran this and it looked like what you wanted... (Note: message 3 should display before message 2 - I did that on purpose to make sure I coded for mistakes if someone typed something wrong).
This is my (limited) understanding of a recursive table configuration - I probably need to be corrected... PK is the Primary Key. ConnectorKey is the value of the message that should connect two or more messages together, (gosh I wish sushila was explaining this - she is so much better than I am...). So, Message 3 has a ConnectorKey of 4, hence many other messages can be connected to Message 3 as long as they have the same ConnectorKey. The datetime then orders the messages for display...
Also, I screwed up by copying and pasting too quickly. It should have been:
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 6, 4, 'Third Message 2nd Addendum', '8/22/2005 4:54:52 PM')
INSERT INTO #Messages( PK, ConnectorKey, Message, MessageDate)
VALUES( 7, 5, 'Fourth Message', '8/22/2005 4:53:52 PM')
(Message 4 would now have a PK of 7 and its own new ConnectorKey of 5; which is not yet attached to any other message).
I wasn't born stupid - I had to study.
-
Actually, it looks like the links in this post are just about perfect. Specifically the link to Manipulating Hierarchies with UDFs; the sample code looks like I can modify it to account for dates.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply