CODEWITHSUNDEEP
sqlcommon-table-expression
Matt
Matt

Reputation: 1911

SQL Parent/Child CTE Ordering

I'm trying to create a forum setup where you can comment on specific posts and have the replies show immediately below the parent in date order. There will only be one sub-level below the parent. Here's the example dataset:

ID.ParentID.Datestamp
12.NULL.2013-03-01 1:00pm
13.NULL.2013-03-01 2:00pm
14.12.2013-03-01 2:20pm
15.12.2013-03-01 2:30pm
16.NULL.2013-03-01 3:30pm

And here's what I'm trying to end up with:

12.NULL.2013-03-01 1:00pm
14.12.2013-03-01 2:20pm
15.12.2013-03-01 2:30pm
13.NULL.2013-03-01 2:00pm
16.NULL.2013-03-01 3:30pm

I know I need some sort of CTE going, but this doesn't order the children under the appropriate parent (obviously, since there's no ORDER BY clause); I couldn't figure out the proper ordering. Can anyone provide some insight?

; WITH Messages
AS 
(
    SELECT ID, ParentID, Datestamp
    FROM ForumMessages
    WHERE ParentID IS NULL

    -- Recursive
    UNION ALL 
    SELECT
        t2.ID, t2.ParentID, t2.Datestamp
    FROM
        ForumMessages AS t2
        JOIN Messages AS m ON t2.ParentID = m.ID
)

SELECT ID, ParentID, Datestamp
FROM Messages

Upvotes: 2

Views: 505

Answers (2)

bendataclear
bendataclear

Reputation: 3850

Very easy way without CTE (ISNULL is the function in MSSQL but you don't give the RDBMS):

SELECT ID, ParentID, Datestamp

FROM ForumMessages

ORDER BY ISNULL(ParentID,ID), DateStamp

Fiddle

Upvotes: 0

user359040
user359040

Reputation:

For a single level depth, you don't need to use recursion - try:

SELECT ID, ParentID, Datestamp
FROM ForumMessages
order by coalesce(ParentID,ID), Datestamp

Upvotes: 3

Related Questions