asked on

Ambiguous Column Name; Non-JOIN Query; Only When ORDER BY Clause is Added

I have read most of the posts on this error but I am not getting it.

This 'Ambiguous column name' error does not happen unitl I introduce the ORDER BY clause and the field.

There's no JOIN of any kind.

There's only the one table in the Query.

SELECT
	TOP 100
	sField001 ,
	sField002 ,
	sField003 ,
	nField004 ,
	nField005 ,
	nField006 ,
	* -- <<<<< notice this inclusion
	FROM
		dbo.tblData -- <<< notice only one table is specified
	WHERE
			sField002 > '20081200000000.000'
		AND	sField003 IS NOT NULL
		AND	sField001 = 'tf'
		AND	nField005 > 1
	ORDER BY
		nField004 ASC  -- <<< ambigous error points here

Open in new window

chapmandew

It is because of the *...it doesn't know which field you want to order by...even though it is the exact same field in the SELECT list. In your scenario, you could change this:

order by nField004

to

order by 4

using this method allows you to order by an ordinal field in the select statement.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tpi007

The column nField004 appears in result set twice - once via explicit column and once via * caluse in select. The query engine cannot distinguiish between the 2 identical column names for order by.

Kevin Cross

Agree with Tim. Why mix use of * there and explicit column names? If you are needing to create new columns with data from existing columns then just give an alias.

SELECT
	TOP 100
	sField001 as s01,
	sField002 as s02,
	sField003 as s03,
	nField004 as n04,
	nField005 as n05,
	nField006 as n06,
	* -- <<<<< notice this inclusion
	FROM
		dbo.tblData -- <<< notice only one table is specified
	WHERE
			sField002 > '20081200000000.000'
		AND	sField003 IS NOT NULL
		AND	sField001 = 'tf'
		AND	nField005 > 1
	ORDER BY
		n04 ASC  -- <<< ambigous error points here

Open in new window

MadraBeag

ASKER

I do not recall this happening in MS SQL 2000. I'll test this. Seems sort of lousy. In my humble opinion, if one specifies columns to display first and one is in the ORDER BY clause, that explicit listed column is sorted not the implicit column found in the * specifier. But that's just me.