TSQL-Order by

Question

Post reply

TSQL-Order by

psingla

Hall of Fame

Points: 3840
More actions
July 13, 2013 at 3:25 pm

#296382

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/
July 15, 2013 at 2:12 am

This was removed by the editor as SPAM

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

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

Mr. Kapsicum SSCertifiable Points: 6128 More actions · Answer 1

Mr. Kapsicum

SSCertifiable

Points: 6128

July 14, 2013 at 10:58 pm

#1632288

easy one...!!! 🙂

Lokesh Vij SSChampion Points: 10836 More actions · Answer 2

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 More actions · Answer 3

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 More actions · Answer 4

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: 6099 More actions · Answer 5

Thanks for start Monday with easy one 🙂

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 6

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 More actions · Answer 7

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: 259085 More actions · Answer 8

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: 10727 More actions · Answer 9

paul s-306273

SSChampion

Points: 10727

July 15, 2013 at 1:48 am

#1632342

Easy easy easy.

Nice start to the week.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 10

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 More actions · Answer 11

Christian Buettner-167247

SSChampion

Points: 13729

July 15, 2013 at 1:57 am

#1632346

Thanks for the question

Best Regards,

Chris Büttner

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 12

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 More actions · Answer 13

Thanks for your replies I got it now 🙂

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