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
,PostedDate DATETIME NOT NULL
)
INSERT INTO @messages (ParentID,[Message],PostedDate)
SELECT NULL,'My first message','2011-03-01 15:00:00' UNION
SELECT NULL,'My second message','2011-03-01 15:05:00' UNION
SELECT 1,'A comment to the first message','2011-03-01 15:02:00' UNION
SELECT 1,'Second comment to the first message','2011-03-01 15:48:00' UNION
SELECT 2,'A comment to the second message','2011-03-01 15:18:00' UNION
SELECT NULL,'My third message','2011-03-01 16:17:00' UNION
SELECT NULL,'My fourth message','2011-03-01 16:58:00' UNION
SELECT 6,'A comment to the third message','2011-03-01 16:19:00' UNION
SELECT 7,'A comment to the fourth message','2011-03-01 17:05:00'
;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
This generates the results as follows:
1 NULL My first message 2011-03-01 15:00:00.000
2 NULL My fourth message 2011-03-01 16:58:00.000
3 NULL My second message 2011-03-01 15:05:00.000
4 NULL My third message 2011-03-01 16:17:00.000
7 2 A comment to the second message 2011-03-01 15:18:00.000
9 7 A comment to the fourth message 2011-03-01 17:05:00.000
5 1 A comment to the first message 2011-03-01 15:02:00.000
6 1 Second comment to the first message 2011-03-01 15:48:00.000
8 6 A comment to the third message 2011-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:
1 NULL My first message 2011-03-01 15:00:00.000
5 1 A comment to the first message 2011-03-01 15:02:00.000
6 1 Second comment to the first message 2011-03-01 15:48:00.000
3 NULL My second message 2011-03-01 15:05:00.000
7 2 A comment to the second message 2011-03-01 15:18:00.000
4 NULL My third message 2011-03-01 16:17:00.000
8 6 A comment to the third message 2011-03-01 16:19:00.000
2 NULL My fourth message 2011-03-01 16:58:00.000
9 7 A comment to the fourth message 2011-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://qa.sqlservercentral.com/articles/T-SQL/72503/[/url]
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
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 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply