Alias Fields - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Alias Fields

Author

Topic

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-10-17 : 14:22:50

If you use an Alias field in a query, should the Alias be used in the ORDER BY clause or does it not matter?

SELECT Cust_N AS CustomerNumber
FROM Customer
ORDER BY CustomerNumber

Or

SELECT Cust_N AS CustomerNumber
FROM Customer
ORDER BY Cust_N

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 14:28:01

Holy sheep

Another M$ "feature"

This would not work in DB2

Did you try it...with SHOWPLAN


USE Northwind
GO

SELECT OrderId AS ORDER_ID
FROM Orders
GROUP BY OrderId

SELECT OrderId AS ORDER_ID
FROM Orders
GROUP BY ORDER_ID

SELECT OrderId AS ORDER_ID
FROM Orders
ORDER BY ORDER_ID
GO

Notice the problem with GROUP BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rwlopez
Yak Posting Veteran

80 Posts

Posted - 2006-10-17 : 14:31:36

I am not sure if I follow your answer. Are you saying it is better to use the actual field name?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 14:32:05

In the ORDER BY, it doesn't matter. I try to use the alias though for clarity.

Tara Kizer

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-17 : 15:58:46

Well SHOWPLAN says that it will change the alias to be the ordinal position in the select, where as when you use the actual column name it uses that

But if you do


SELECT CONVERT(varchar(25),OrderId) AS ORDER_ID
FROM Orders
GROUP BY OrderId

SELECT CONVERT(varchar(25),OrderId) AS ORDER_ID
FROM Orders
ORDER BY ORDER_ID
GO

It does more work

You need to look at what show plan tells you

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-17 : 16:07:10

How does the ordinal position impact the plan though?

GROUP BY is a whole different story.

Tara Kizer

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-18 : 09:27:44

If you want to use alias name, better make use of Derived table for better clarity

Select alias,... from
(
Select.......
) T order by alias --or group by alias

Madhivanan

Failing to plan is Planning to fail

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-18 : 16:32:23

My turn to say holy grass-eating-critter, why make a simple answer so complicated.

In simple cases, like in your question, it doesn't matter, and I fully agree with Tara, that using an alias can make it easier to read and clear intentions.

Brett you are confusing matters, you can't use aliases in a GROUP BY (but have to use derived tables like Madhi suggests), and cases where it DOES matter (like in CONVERT(varchar(25),OrderId) AS ORDER_ID), you are actually NOT asking for the same, although in your example you do get the same order (but sorted according to either an INT or a VARCHAR).

So all in all the simple answer is, do as you see fit, but be sure you know what ask for

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-10-18 : 16:35:07

... you can of course also write

SELECT Cust_N AS CustomerNumber
FROM Customer
ORDER BY 1

but you wont see that used often on this site.

-- Alice came to a fork in the road. "Which road do I take?" she asked. "Where do you want to go?" responded the Cheshire cat. "I don't know," Alice answered. "Then," said the cat, "it doesn't matter."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-20 : 12:38:34

>>but you wont see that used often on this site.

Becuase it is not ANSI standard?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources