CTE Problem Redux

  • 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

  • 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

  • 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

  • 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.

  • Interesting question, interesting discussion - thanks, sknox!

  • Kool one

    Thanks.

  • 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

  • +1

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

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