Post reply

alternative to cte

  • January 23, 2012 at 3:34 pm

    #391378

    I was wondering if someone here knew how to write another select statement that wasn't a cte. This displays a list of posts in their heirarchy. Does anyone else know how to write this better. I need a Level and Url the root url of the post

    WITH cte(Url, Level, Path, PostID, AddedBy, AddedDate, ForumID, ParentPostID, Title, Body,

    Approved, Closed, Sticky, ViewCount, ReplyCount, LastPostBy, LastPostDate, UpdatedBy, UpdatedDate, Active)

    AS (

    SELECT REPLACE(LOWER(Title), ' ', '-'), 0 AS Level, CAST(PostID as nvarchar(MAX)) as Path,

    PostID, AddedBy, AddedDate, ForumID, ParentPostID, Title, Body,

    Approved, Closed, Sticky, ViewCount, ReplyCount, LastPostBy, LastPostDate, UpdatedBy, UpdatedDate, Active

    FROM Post

    WHERE ParentPostID IS NULL

    UNION ALL

    SELECT cte.Url, cte.Level + 1,

    LOWER(cte.Path + '-' + CAST(P.PostID AS VARCHAR(MAX))),

    P.PostID, P.AddedBy, P.AddedDate, P.ForumID, P.ParentPostID, P.Title, P.Body,

    P.Approved, P.Closed, P.Sticky, P.ViewCount, P.ReplyCount, P.LastPostBy, P.LastPostDate, P.UpdatedBy, P.UpdatedDate, P.Active

    FROM Post P

    INNER JOIN cte ON P.ParentPostID = cte.PostID

    )

    SELECT * FROM cte WHERE Url = 'entity-framework' ORDER BY Path, ParentPostID, PostID, AddedDate ASC

  • January 23, 2012 at 11:53 pm

    #1437482

    Sure , investigate the use of heirarchyId.


    Clear Sky SQL
    My Blog[/url]

  • January 24, 2012 at 12:16 am

    #1437491

    kirkdm01 (1/23/2012)

    I was wondering if someone here knew how to write another select statement that wasn't a cte. This displays a list of posts in their heirarchy. Does anyone else know how to write this better. I need a Level and Url the root url of the post

    ...

    Probably not without changing datatypes as Dave has suggested. If performance is the problem, try putting an index on ParentPostID.

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 24, 2012 at 2:59 am

    #1437537

    To increase performance of this as suggested create index on ParentPostID and add all columns in select as included.

    Thanks,
    GG;-)

  • January 24, 2012 at 3:30 am

    #1437560

    how do you create an index on ParentPostID im new to SQL so im just learning. What do i do

  • January 24, 2012 at 3:41 am

    #1437562

    http://msdn.microsoft.com/en-us/library/ms188783.aspx

    Refer Example G. Creating an index with included (non-key) columns

    Thanks,
    GG;-)

  • January 24, 2012 at 3:41 am

    #1437563

    But doesn't HeirarchyId only have one root where posts have more than one root eg PostParentID = null then the children, I thought heirarchyId has one root

  • January 24, 2012 at 3:47 am

    #1437566

    PostParentID is one

    Thanks,
    GG;-)

  • January 24, 2012 at 4:07 am

    #1437574

    kirkdm01 (1/24/2012)

    how do you create an index on ParentPostID im new to SQL so im just learning. What do i do

    What's your working environment, Kirk? Are you on your own/do you have a DBA etc? What changes can you make to table structures? Is this study or work?

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply