CTE Problem Redux

  • Rich Mechaber

    SSChampion

    Points: 10935

    SQLRNNR (4/28/2015)


    Hey, so far it is improved in % correct. Only 53% got it correct when I asked a very similar question a few years back.

    http://www.sqlservercentral.com/questions/Subquery/75557/

    Well, I got today's right but yours from 2011 wrong. I am left with the conclusion that I must have learned something 3-1/2 years ago from you!

    (How's that for delayed "thanks I learned something" for ya?)

    As I said back in 2011 and is still true today: I never use the feature of CTEs aliasing column names in parentheses, b/c I find it more reliable to simply always code with one method of aliasing, in my SELECT list. That works regardless of query type.

    Is there in fact any benefit aliasing the columns in the CTE?

    Rich

  • SQLRNNR

    SSC Guru

    Points: 281252

    rmechaber (4/28/2015)


    SQLRNNR (4/28/2015)


    Hey, so far it is improved in % correct. Only 53% got it correct when I asked a very similar question a few years back.

    http://www.sqlservercentral.com/questions/Subquery/75557/

    Well, I got today's right but yours from 2011 wrong. I am left with the conclusion that I must have learned something 3-1/2 years ago from you!

    (How's that for delayed "thanks I learned something" for ya?)

    As I said back in 2011 and is still true today: I never use the feature of CTEs aliasing column names in parentheses, b/c I find it more reliable to simply always code with one method of aliasing, in my SELECT list. That works regardless of query type.

    Is there in fact any benefit aliasing the columns in the CTE?

    Rich

    Occasionally it is necessary or beneficial. For the most part, I just alias in the select as you do.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Rich Mechaber

    SSChampion

    Points: 10935

    SQLRNNR (4/28/2015)


    rmechaber (4/28/2015)


    SQLRNNR (4/28/2015)


    Hey, so far it is improved in % correct. Only 53% got it correct when I asked a very similar question a few years back.

    http://www.sqlservercentral.com/questions/Subquery/75557/

    Well, I got today's right but yours from 2011 wrong. I am left with the conclusion that I must have learned something 3-1/2 years ago from you!

    (How's that for delayed "thanks I learned something" for ya?)

    As I said back in 2011 and is still true today: I never use the feature of CTEs aliasing column names in parentheses, b/c I find it more reliable to simply always code with one method of aliasing, in my SELECT list. That works regardless of query type.

    Is there in fact any benefit aliasing the columns in the CTE?

    Rich

    Occasionally it is necessary or beneficial. For the most part, I just alias in the select as you do.

    "necessary"? OK, now I'm curious. Do you happen to have an example?

    Rich

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Question which seems easy according to me.

    After the previous question about CTE , I have (re)studied CTE.

    For the names of the columns in the display , I am preferring aliases (?) defined in the queries ( thru AS ) as often it is more pleasant to read in an application ( titles of the columns are easier to understand especially for non specialist users of SQL Server ) or if I am using a DataTable in my application , I am filling the Caption property of the DataColumn with an expression clearer to understand than the name of the column. An habit not always appreciated by products managers but usually , the "lambda" user appreciates as often I let him to choose the caption of the column. But I have usually worked in small companies which appreciated this way.

  • Revenant

    SSC-Forever

    Points: 42467

    Interesting question, interesting discussion - thanks, sknox!

  • SQL_Hunt

    SSC-Dedicated

    Points: 33450

    Kool one

    Thanks.

  • TomThomson

    SSC Guru

    Points: 104773

    SQLRNNR (4/28/2015)


    Hey, so far it is improved in % correct. Only 53% got it correct when I asked a very similar question a few years back.

    http://www.sqlservercentral.com/questions/Subquery/75557/

    I wonder how many people took one look at that, said to themselves "if anyone writes queirs like they should be trained not to because it's a jumbled mess", decided they couldn't be bothered to parse it by hand, and picked an answer at random to see what came out.

    As Paul said at the time, "The question could have made the same point, with the same learning value, much more simply".

    To could be added "and without mixing CTE and subquery together in a join when there's no conceivable reason not to do either both as CTEs or both as subquries, resulting in rather ugly SQL".

    Tom

  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    +1

Viewing 8 posts - 16 through 23 (of 23 total)

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