Ambiguous ORDER BY Column - 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 2008 Forums

Transact-SQL (2008)

Ambiguous ORDER BY Column

Author

Topic

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 09:54:10

I've migrated some code from SQL 2000 to SQL 2008 and I'm getting an "Ambiguous" error on my Order By.

Trouble is I'm not sure what SQL 2000 did to be certain that I can fix it correctly.

Here's the code


SELECT TOP 100
	[ColX] = P1.ColA,
	[ColY] = P2.ColA,
	ColB,
	[ColX_NEW] = ColX,
	[ColY_NEW] = ColY
FROM	dbo.Table99
	JOIN dbo.Table98 AS P1
		 ON P1.ColC = ColX
	JOIN dbo.Table98 AS P2
		 ON P2.ColC = ColY
ORDER BY ColX, ColY, P1.ColA

I *think* SQL 2000 was using the Alias name in the Select (rather than the original table names)

ColX and ColY are both in Table99.

I can see that it isn't entirely unambiguous but Hey! SQL 2000 was happy with it!!

How do you think SQL 2000 treated this?

Actual error is:

Server: Msg 169, Level 15, State 1, Procedure MySProc, Line 179
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-29 : 11:39:34

The issue is that SQL 2008 (and 2005 I think) will let you order by the Alias. Sice you have an Alias and Columns names that are the same I suspect that SQL doesn't like that. So, adding the schema to the orderby columns should solve the issue.

EDIT: The solution is the same, but I was too quick on the draw and it appears that SQL is using the Alias when a schema is not specified so it is, in affect, ordering by P1.ColA, P2.ColA, and P1.ColA (again). So it's ordering by ColA twice. I hope tht makes sense.. :)

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 12:44:41

Thanks Lamprey

"SQL is using the Alias when a schema is not specified so it is, in affect, ordering by P1.ColA, P2.ColA, and P1.ColA (again)"

That's my conclusion too. Unfortunately P1.ColA and ColX are identical, so I can't tell (on SQL 2000) what the order would acutally be.

Actually .... typing that I only need to make a little test file and a couple of rows and I can test it out Doh!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-29 : 14:05:59

quote:
Originally posted by Kristen

That's my conclusion too. Unfortunately P1.ColA and ColX are identical, so I can't tell (on SQL 2000) what the order would acutally be.

There is a slight confusion. P1.ColA and ColX are the same to 2005 & 2008 becuase of the alias, but sql 2000 they are different because ColX is not the aliased column, but the column from the table (Table99 yes?). So, for all flavors of SQL the ORDER BY should use the correct schema to make sure there is no confusion. IE: P1.ColA, Table99.ColX

I hope that makes sense. It gets a little wonky to talk about columns with other columns alaised the as the same name. :)

Kristen
Test

22859 Posts

Posted - 2010-01-29 : 14:11:00

Sorry, I meant "P1.ColA and ColX [i.e. the one in Table99] contain identical data" so whatever I do the sort is correct, and I thus don't know what we originally intended the sort order should be ... or so I thought ... of course what I need to do, to be 100% sure, is to set up some sample data where the data in those two columns is NOT the same, and see which column they sort by. Then give them proper fully qualified names

I wish that SQL 2000 had complained ... or we had STRICT mode ... or SQL LINT ... to warn me about by sloppy coding

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-29 : 14:14:06

Table99!!

I have a copyright damn it

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-01-29 : 14:37:14

What about tables named Table100 - Table999? Just wanna make sure I avoid any copyright stuff for all my future applicaitons.. ;)

Kristen
Test

22859 Posts

Posted - 2010-01-30 : 02:25:32

"I have a copyright damn it"

Oh! I thought you were just teaching that style, no?!!

I started doing Find&Replace of Tables and Columns to get a simple example, and I got to


FROM	dbo.Table1
	JOIN dbo.Table2 AS P1
		 ON P1.ColC = ColX
	JOIN dbo.Table2 AS P2
		 ON P2.ColC = ColY

and then I thought that Table2 AS P1 and Table2 AS P2 was going to be confusing

, so I had a brainwave - change Table Numbers to 98, 99.

How long have you been teaching me this? Must be very frustrating how long it takes students to cotton-on ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 01:09:00

Have you seen any points regarding the alias name in the behavioural changes link from Microsoft?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-01 : 01:23:59

quote:

Originally posted by Kristen


SELECT TOP 100
	[ColX] = P1.ColA,
	[ColY] = P2.ColA,
	ColB,
	[ColX_NEW] = ColX,
	[ColY_NEW] = ColY
FROM	dbo.Table99
	JOIN dbo.Table98 AS P1
		 ON P1.ColC = ColX
	JOIN dbo.Table98 AS P2
		 ON P2.ColC = ColY
ORDER BY ColX, ColY, P1.ColA

The problem is the P1.ColA, in SQL 2005/2008 once you have alias a column, you should only reference it via the alias name.

In SQL 2000, i guess the parser just closed one eye and let it pass

I guess it is just to be consistent with the table name alias rule. You can't reference the table name when you have already alias it.
Like the code below.


select Table98.ColA
from   Table98 P1

This is totally not allow in SQL 2000/2005/2008

KH
[spoiler]Time is always against us[/spoiler]

Kristen
Test

22859 Posts

Posted - 2010-02-01 : 06:27:41

Here's some tests I made:


DROP TABLE #TEMP
GO
CREATE TABLE #TEMP
(
	MyCol1 varchar(10),
	MyCol2 varchar(10)
)

INSERT INTO #TEMP
SELECT 'AAA', 'ZZZ' UNION ALL
SELECT 'BBB', 'YYY' UNION ALL
SELECT 'CCC', 'XXX' UNION ALL
SELECT 'DDD', 'WWW'

SELECT	MyCol1, MyCol2
FROM	#TEMP AS T
ORDER BY MyCol1, MyCol2

All versions


MyCol1     MyCol2     
---------- ---------- 
AAA        ZZZ
BBB        YYY
CCC        XXX
DDD        WWW


SELECT	[MyCol2]=MyCol1, [MyCol1]=MyCol2
FROM	#TEMP AS T
ORDER BY MyCol1, MyCol2

SQL 2000 - by Table/Column names in preference to Select alias:


MyCol2     MyCol1     
---------- ---------- 
AAA        ZZZ
BBB        YYY
CCC        XXX
DDD        WWW

SQL 2005 / 2008 - by Select Alias:


MyCol2     MyCol1     
---------- ---------- 
DDD        WWW
CCC        XXX
BBB        YYY
AAA        ZZZ


SELECT	[MyCol2]=MyCol1, MyCol2
FROM	#TEMP AS T
ORDER BY T.MyCol1, T.MyCol2

SQL 2000 - Priorities Table/Column Name but can't handle Table Alias to that column if Select Alias present (looks like bug to me):

Server: Msg 169, Level 15, State 1, Line 1
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

SQL 2005 / 2008 - Explicit Table Alias overrides Select alias:


MyCol2     MyCol2     
---------- ---------- 
AAA        ZZZ
BBB        YYY
CCC        XXX
DDD        WWW


SELECT	[MyCol2]=MyCol1, [FooBar]=MyCol2
FROM	#TEMP AS T
ORDER BY MyCol1, MyCol2

SQL 2000 - prioritises Table/Column name over Select Alias


MyCol2     FooBar     
---------- ---------- 
AAA        ZZZ
BBB        YYY
CCC        XXX
DDD        WWW

SQL 2005 / 2008 - now thinks that Select Alias and Table Name/Column clash

Server: Msg 169, Level 15, State 1, Line 3
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-01 : 07:57:39

Any answer to my question?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 08:55:11

quote:
Originally posted by madhivanan

Any answer to my question?

Madhivanan

Failing to plan is Planning to fail

it is specified in behavioral changes doc from MS

ORDER BY clause
-----------------
SQL 2000
--------------

Column names in the ORDER BY clause are resolved to columns listed in the select list, regardless if they are qualified.
For example, the following query executes without error:

USE pubs
SELECT au_fname AS 'FName',
au_lname AS 'LName'
FROM authors a
ORDER BY a.LName

SQL Server ignores the qualifier a in the ORDER BY clause and resolves the column name LName to the select list.

SQL 2005
-------------
Qualified column names and aliases are resolved to columns of tables listed in the FROM clause. If order_by_expression is not qualified, it must be unique among all columns listed in the SELECT statement.
For example, the following equivalent query returns an error:

USE AdventureWorks
SELECT FirstName AS 'FName',
LastName AS 'LName'
FROM Person.Contact p
ORDER BY p.LName

SQL Server does not ignore the qualifier p in the ORDER BY clause, and resolves the column name LName to tables listed in the FROM clause. But the FROM clause does not recognize that column LName is a column alias of table p.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-01 : 08:55:32

here's the link

http://msdn.microsoft.com/en-us/library/ms143359(SQL.90).aspx

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 08:57:16

Another post with behavioural changes. It doesn't break the code but gives different results

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=139227

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-02 : 09:28:14

quote:
Originally posted by Kristen

I've migrated some code from SQL 2000 to SQL 2008 and I'm getting an "Ambiguous" error on my Order By.

Trouble is I'm not sure what SQL 2000 did to be certain that I can fix it correctly.

Here's the code

SELECT TOP 100
[ColX] = P1.ColA,
[ColY] = P2.ColA,
ColB,
[ColX_NEW] = ColX,
[ColY_NEW] = ColY
FROM dbo.Table99
JOIN dbo.Table98 AS P1
ON P1.ColC = ColX
JOIN dbo.Table98 AS P2
ON P2.ColC = ColY
ORDER BY ColX, ColY, P1.ColA

I *think* SQL 2000 was using the Alias name in the Select (rather than the original table names)

ColX and ColY are both in Table99.

I can see that it isn't entirely unambiguous but Hey! SQL 2000 was happy with it!!

How do you think SQL 2000 treated this?

Actual error is:

Server: Msg 169, Level 15, State 1, Procedure MySProc, Line 179
A column has been specified more than once in the order by list. Columns in the order by list must be unique.

This is the behavioural changes which is documented in BOL at
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-02 : 13:00:54

Thanks Madhi.

My objective was to work out what the behaviour was in SQL 2000 so I could make that when I fixed it for SQL 2008 I was duplicating the original behaviour!

My second test is a bit scary - no error message on either system, but behaviour has changed. That will be harder to find ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources