TSQL-Order by

  • psingla

    Hall of Fame

    Points: 3840

    Comments posted to this topic are about the item TSQL-Order by

    Pramod
    SQL Server DBA | MCSE SQL Server 2012/2014

    in.linkedin.com/in/pramodsingla/
    http://pramodsingla.wordpress.com/

  • Mr. Kapsicum

    SSCertifiable

    Points: 6128

    easy one...!!! πŸ™‚

  • Lokesh Vij

    SSChampion

    Points: 10836

    Easy one for Monday morning πŸ™‚

    Thanks Pramod!

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • kapil_kk

    SSC-Insane

    Points: 21316

    Lokesh Vij (7/14/2013)


    Easy one for Monday morning πŸ™‚

    Thanks Pramod!

    +1 πŸ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • kapil_kk

    SSC-Insane

    Points: 21316

    When I checked the BOL i found that there is a line stating:

    If a table name is aliased in the FROM clause, only the alias name can be used to qualify its columns in the ORDER BY clause.

    I did some modification in the query and try to run following scenarios and these all are working.

    select a ss from test t

    order by ss

    select t.a ss from test t

    order by a

    select a from test t

    order by a

    Can anyone tell me what does that BOL line means?

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Thanks for start Monday with easy one πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    kapil_kk (7/14/2013)


    When I checked the BOL i found that there is a line stating:

    If a table name is aliased in the FROM clause, only the alias name can be used to qualify its columns in the ORDER BY clause.

    I did some modification in the query and try to run following scenarios and these all are working.

    select a ss from test t

    order by ss

    select t.a ss from test t

    order by a

    select a from test t

    order by a

    Can anyone tell me what does that BOL line means?

    According to BOl, If you have given a alias name to column name then you can not use column name in order by clause.

    the 2nd statement should give error like "invalid column name "a" ", but it doesn't. I am also looking for reason why 2nd T-sql code execute successfully.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • nenad-zivkovic

    Default port

    Points: 1448

    You haven't read it carefully. It says: "If a table name is aliased, only the alias name can be used to qualify its columns in the ORDER BY clause."

    So this will not work:

    select a from test t

    order by test.a

    _______________________________________________
    www.sql-kefalo.net (SQL Server saveti, ideje, fazoni i fore)

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Interesting question for a Monday πŸ˜€

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • paul s-306273

    SSChampion

    Points: 10615

    Easy easy easy.

    Nice start to the week.

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    Danny Ocean (7/14/2013)


    kapil_kk (7/14/2013)


    When I checked the BOL i found that there is a line stating:

    If a table name is aliased in the FROM clause, only the alias name can be used to qualify its columns in the ORDER BY clause.

    I did some modification in the query and try to run following scenarios and these all are working.

    select a ss from test t

    order by ss

    select t.a ss from test t

    order by a

    select a from test t

    order by a

    Can anyone tell me what does that BOL line means?

    According to BOl, If you have given a alias name to column name then you can not use column name in order by clause.

    the 2nd statement should give error like "invalid column name "a" ", but it doesn't. I am also looking for reason why 2nd T-sql code execute successfully.

    Thanks i got it... πŸ™‚

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Thanks for the question

    Best Regards,

    Chris BΓΌttner

  • This was removed by the editor as SPAM

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    nenad-zivkovic (7/15/2013)


    You haven't read it carefully. It says: "If a table name is aliased, only the alias name can be used to qualify its columns in the ORDER BY clause."

    So this will not work:

    select a from test t

    order by test.a

    +1

    But this applies for the whole query, not only to the ORDER BY clause. It's funny, though, that if you alias the column, both names can be used (original & alias) in the ORDER BY clause

    Create table #test(a int)

    insert into #test values (null)

    insert into #test values (2)

    insert into #test values (3)

    insert into #test values (1)

    insert into #test values (null)

    -- Will work

    select a as col1 from #test as t

    order by col1 -- ORDER BY alias

    select a as col1 from #test as t

    order by a -- ORDER BY column name

    -- Will NOT work

    select #test.a as col1 from #test as t

    order by col1

    select a as col1 from #test as t

    order by #test.a

    drop table #test

    Cheers

  • kapil_kk

    SSC-Insane

    Points: 21316

    Thanks for your replies I got it now πŸ™‚

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 15 posts - 1 through 15 (of 25 total)

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