Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

TSQL-Order by Expand / Collapse
Author
Message
Posted Monday, July 15, 2013 1:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
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 !!!
www.GrowWithSql.com

Post #1473513
Posted Monday, July 15, 2013 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:37 AM
Points: 2,842, Visits: 3,873
Thanks for the question

Best Regards,
Chris Büttner
Post #1473516
Posted Monday, July 15, 2013 2:12 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:47 AM
Points: 3,951, Visits: 5,180
Interesting question, thanks - NULLs always elicit interesting (and ofttimes undesired) results if not handled properly

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1473520
Posted Monday, July 15, 2013 5:06 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 8:48 AM
Points: 608, Visits: 889
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
Post #1473578
Posted Monday, July 15, 2013 5:17 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 3:27 AM
Points: 1,890, Visits: 2,329
Thanks for your replies I got it now :)


_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1473585
Posted Monday, July 15, 2013 5:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 5:37 AM
Points: 2,842, Visits: 3,873
raulggonzalez (7/15/2013)
It's funny, though, that if you alias the column, both names can be used (original & alias) in the ORDER BY clause

Try to think of the column alias as a very simple expression
So Col1 AS X = 1 * Col1 AS X

In queries you can sort by expressions AND by the original table columns, even if they are not included in the output. If I include the original column in the output, it should become more clear:
SELECT a AS x -- "Computed" column
,a -- "Original" column
FROM(SELECT 1 AS a) d
ORDER BY
x
,a

[Edit: Added query comments]


Best Regards,
Chris Büttner
Post #1473594
Posted Monday, July 15, 2013 7:29 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 8:40 AM
Points: 1,877, Visits: 1,409
Ezpz. Thanks for the question.



Everything is awesome!
Post #1473646
Posted Monday, July 15, 2013 10:29 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:10 PM
Points: 2,318, Visits: 1,375
Good Monday question. Nice explanation as well.

Not all gray hairs are Dinosaurs!
Post #1473770
Posted Tuesday, July 16, 2013 5:12 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 3:19 AM
Points: 5,363, Visits: 1,391
Nice one....


Post #1474064
Posted Tuesday, July 16, 2013 5:25 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 4:14 AM
Points: 267, Visits: 235
nice question....

Manik
You cannot get to the top by sitting on your bottom.
Post #1474068
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse