March 11, 2011 at 3:29 am
Hi all,
I have a table that contains message posts and within the same table contains message comments. To identify an actual post rather than a comment, I have used a ParentID INT column that refers to the parent message. Only comments have a ParentID that isn't NULL. Simple so far. My requirement is to list all the messages along with their comments in a hierarchical fashion, ordered by date so that each message is followed by it's comments, also in date order. For example:
DECLARE @messages TABLE (
MessageID INT PRIMARY KEY IDENTITY(1,1) NOT NULL
,ParentID INT NULL
,[Message] VARCHAR(100)NOT NULL
,PostedDateDATETIME NOT NULL
)
INSERT INTO @messages (ParentID,[Message],PostedDate)
SELECTNULL,'My first message','2011-03-01 15:00:00' UNION
SELECTNULL,'My second message','2011-03-01 15:05:00' UNION
SELECT1,'A comment to the first message','2011-03-01 15:02:00' UNION
SELECT1,'Second comment to the first message','2011-03-01 15:48:00' UNION
SELECT2,'A comment to the second message','2011-03-01 15:18:00' UNION
SELECTNULL,'My third message','2011-03-01 16:17:00' UNION
SELECTNULL,'My fourth message','2011-03-01 16:58:00' UNION
SELECT6,'A comment to the third message','2011-03-01 16:19:00' UNION
SELECT7,'A comment to the fourth message','2011-03-01 17:05:00'
;WITH msgHierarchy AS (
SELECTmsg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM@messages msg
WHEREParentIDIS NULL
UNION ALL
SELECTmsg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM@messages msg INNER JOIN
msgHierarchy mh
ON(mh.MessageID= msg.ParentID)
)
SELECT*
FROMmsgHierarchy
This generates the results as follows:
1NULLMy first message2011-03-01 15:00:00.000
2NULLMy fourth message2011-03-01 16:58:00.000
3NULLMy second message2011-03-01 15:05:00.000
4NULLMy third message2011-03-01 16:17:00.000
72A comment to the second message2011-03-01 15:18:00.000
97A comment to the fourth message2011-03-01 17:05:00.000
51A comment to the first message2011-03-01 15:02:00.000
61Second comment to the first message2011-03-01 15:48:00.000
86A comment to the third message2011-03-01 16:19:00.000
My question is how do I get the results to actually display hierarchically? I've played around with the ORDER BY clause but to no avail. Using the CTE, I would've expected the following result set:
1NULLMy first message2011-03-01 15:00:00.000
51A comment to the first message2011-03-01 15:02:00.000
61Second comment to the first message2011-03-01 15:48:00.000
3NULLMy second message2011-03-01 15:05:00.000
72A comment to the second message2011-03-01 15:18:00.000
4NULLMy third message2011-03-01 16:17:00.000
86A comment to the third message2011-03-01 16:19:00.000
2NULLMy fourth message2011-03-01 16:58:00.000
97A comment to the fourth message2011-03-01 17:05:00.000
I've scoured the internet for examples, but they all result in the same.
Thank you in advance.
Kev.
March 11, 2011 at 4:01 am
Nice post Kev... complete with all you need to provide a tested solution.
The creation of your test data may be what is stopping you from coming up with the solution yourself.
Your messageid (with the IDENTITY) is not being created in the sequence you expect.
Just try populating it, and do a SELECT * from @messages... hopefully, you'll see what I mean.
I've adjusted your data creation to manually populate the message id in the right sequence, and provided the SELECT statement at the botton that gives you what you need (I think).
DECLARE @messages TABLE (
MessageID INT PRIMARY KEY NOT NULL
,ParentID INT NULL
,[Message] VARCHAR(100) NOT NULL
,PostedDate DATETIME NOT NULL
)
INSERT INTO @messages (messageid,ParentID,[Message],PostedDate)
SELECT 1,NULL,'My first message','2011-03-01 15:00:00' UNION
SELECT 2,NULL,'My second message','2011-03-01 15:05:00' UNION
SELECT 3,1,'A comment to the first message','2011-03-01 15:02:00' UNION
SELECT 4,1,'Second comment to the first message','2011-03-01 15:48:00' UNION
SELECT 5,2,'A comment to the second message','2011-03-01 15:18:00' UNION
SELECT 6,NULL,'My third message','2011-03-01 16:17:00' UNION
SELECT 7,NULL,'My fourth message','2011-03-01 16:58:00' UNION
SELECT 8,6,'A comment to the third message','2011-03-01 16:19:00' UNION
SELECT 9,7,'A comment to the fourth message','2011-03-01 17:05:00'
--select * from @messages
;WITH msgHierarchy AS (
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg
WHERE ParentID IS NULL
UNION ALL
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg INNER JOIN
msgHierarchy mh
ON( mh.MessageID = msg.ParentID)
)
SELECT *, case when parentid IS NULL then messageid else parentid end as sort_key
FROM msgHierarchy
order by sort_key,PostedDate
March 11, 2011 at 4:07 am
Let it be said Ian...you're a legend!
I've just run it up and it's spot on what I need. I think the key was also in the
case when parentid IS NULL then messageid else parentid end as sort_key
part of the final SELECT...I was trying different things just using the static values I had.
Nice one mate, much MUCH appreciated!
March 11, 2011 at 4:43 am
kp81 (3/11/2011)
Let it be said Ian...you're a legend!I've just run it up and it's spot on what I need. I think the key was also in the
case when parentid IS NULL then messageid else parentid end as sort_keypart of the final SELECT...I was trying different things just using the static values I had.
Nice one mate, much MUCH appreciated!
Hi Kev, here's an excellent article covering the subject:
http://www.sqlservercentral.com/articles/T-SQL/72503/%5B/url%5D
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 11, 2011 at 4:47 am
kp81 (3/11/2011)
Nice one mate, much MUCH appreciated!
My pleasure, and thanks for the feedback.
March 11, 2011 at 5:04 am
Hi Kev, here's an excellent article covering the subject:
http://www.sqlservercentral.com/articles/T-SQL/72503/%5B/url%5D
Cheers
ChrisM
Thanks Chris.
March 13, 2011 at 6:16 am
Guys,
I've been playing with the code (works great btw), however I have one issue. I'd like to order the items by messages DESC, but comments ASC - if that's possible?? If not, I don't mind ordering both DESC. By just adding DESC to the following code, it messes up all the previous good work.
SELECT *, case when parentid IS NULL then messageid else parentid end as sort_key
FROM msgHierarchy
order by sort_key,PostedDate DESC
Any ideas? Do I need to build another composite key from the PostedDate and the sort_key?
Thanks again,
Kev.
March 13, 2011 at 11:17 am
kp81 (3/13/2011)
Guys,I've been playing with the code (works great btw), however I have one issue. I'd like to order the items by messages DESC, but comments ASC - if that's possible?? If not, I don't mind ordering both DESC. By just adding DESC to the following code, it messes up all the previous good work.
SELECT *, case when parentid IS NULL then messageid else parentid end as sort_key
FROM msgHierarchy
order by sort_key,PostedDate DESC
Any ideas? Do I need to build another composite key from the PostedDate and the sort_key?
Thanks again,
Kev.
Simple fix, put the DESC on sort_key, like this:
WITH msgHierarchy AS (
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg
WHERE ParentID IS NULL
UNION ALL
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg INNER JOIN
msgHierarchy mh
ON( mh.MessageID = msg.ParentID)
)
SELECT *, case when ParentID IS NULL then MessageID else ParentID end as sort_key
FROM msgHierarchy
order by sort_key desc,PostedDate;
March 13, 2011 at 11:21 am
Two alternatives to Ian's code as well (minor tweaks) still using his setup:
WITH msgHierarchy AS (
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg
WHERE ParentID IS NULL
UNION ALL
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg INNER JOIN
msgHierarchy mh
ON( mh.MessageID = msg.ParentID)
)
SELECT *
FROM msgHierarchy
order by
case when ParentID IS NULL then MessageID else ParentID end desc,
PostedDate;
WITH msgHierarchy AS (
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg
WHERE ParentID IS NULL
UNION ALL
SELECT msg.MessageID, msg.ParentID, msg.[Message], msg.PostedDate
FROM @messages msg INNER JOIN
msgHierarchy mh
ON( mh.MessageID = msg.ParentID)
)
SELECT *
FROM msgHierarchy
order by
coalesce(ParentID, MessageID) desc,
PostedDate;
March 13, 2011 at 3:43 pm
Hi Lynn,
Thanks for your posts.
Your final "coalesce" example has worked a treat and meant that messages are displayed newest first (DESC) and comments newest last (ASC).
Genius!!
Thanks again,
Kev.
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply