Geek Answers Handbook

Explain the cause of the ambiguous column error in SQL Server 2008

I have a Business_Unit table:

  business_unit_id int
     area_code nvarchar (100)
     region_code nvarchar (100)
     sub_region_code nvarchar (100)

It has some meanings.

Request 1:

 select business_unit_id,* from business_unit order by business_unit_id desc

When I request this, I get the following error.

Msg 209, Level 16, State 1, Line 1
The ambiguous column name is "business_unit_id".

To solve, I used the alias name for the table as bu and the column prefix with the alias.

 select bu.business_unit_id, * from business_unit bu order by bu.business_unit_id desc

Even the following request is executed.

 select bu.business_unit_id, bu.* from business_unit bu order by bu.business_unit_id desc

I would like to know why this caused the error [business_unit_id], with the query "Query 1". There is no ambiguity, I have only one table.

Can you explain?

reason to request this question. I have a column of 120 columns (suppose a large column), now I want to order it, say, 90 columns. I cannot scroll and check the value, so I put select 90thcolumn, * from a large table order on the 90th column.

+6
source share
4 answers

Right. I think I found some explanation for this strange behavior.

IF you only do something like this

 SELECT Column1, * FROM Table_Name

this should work fine.

But when you do something like

 SELECT Column1, * FROM Table_Name ORDER BY Column1 --<-- this Column1 is contained in `*` as well as in the SELECT -- statement too, SQL Server needs to know which one to use -- in your order by clause.

It throws an error, because Column1 is twice SELECTED selected in SELECT Query, and SQL Server wants to know which column you want to order by your results.

The ambiguous column is in your Order by clause, but not in your Select statement.

Further explanation

Next, to prove your point, the following order of execution of SQL commands.

  • FROM clause

  • WHERE clause

  • GROUP BY clause

  • HAVING offer

  • SELECT clause

  • ORDER BY clause

As you can see, the SELECT statement is executed before the ORDER BY clause. so in your case, the SELECT clause will have two columns with the same name, and when it comes to ORDER BY, SQL Server results want to know which column to use in your ORDER BY, and it throws an Ambiguous column error.

When used with an alias, Ambiguity resolves and you get no more errors.

+5
source

This is because you use a wildcard * , which returns all rows. Change your request to ...

 Select * from business_unit order by business_unit_id desc;

And by the way, you really can "trick" the SQL server using aliases

+2
source

You get an Ambiguous column name 'business_unit_id' error due to an order by clause. The sql server allows you to include two columns with the same name included in the query (without joining).

Thus, the order by expression cannot determine which column to use when sorting, because it can see two columns with the same name.

+1
source
Answer

@ M.Ali explains the problem well that one work around is an alias of only the column, there is no need for an alias of the whole table:

 select business_unit_id bid,* from business_unit order by business_unit_id desc
+1
source

More articles:


All Articles