Explain the reason for Ambiguous column error in SQL Server 2008

Question

I have a table Business_Unit:

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

It has some values in it.

Query 1:

select 
    business_unit_id,* 
from 
    business_unit 
order by 
    business_unit_id desc

When I query this, i get the following error.

Msg 209, Level 16, State 1, Line 1
Ambiguous column name 'business_unit_id'.

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

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

Even the below query works.

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

I would like to know, why it threw an error[business_unit_id], with query "Query 1". There is no ambiguity here, I only have one table.

Can you explain?

---

the reason for asking this question. I have a 120 column table (assume bigtable), now, I want to order it by let say 90 column. I cannot scroll and check the value, hence I put select 90thcolumn,* from bigtable order by 90thcolumn.

---

Answer 1

Right I think I have found some explanation for this weird behaviour

IF you only do something like this

SELECT Column1, * FROM Table_Name  

this should work ok.

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 will throw an error as Column1 is being SELECTED twice in you SELECT Query and SQL Server wants to know by which column you want to order by your Results .

Ambiguous column is in your Order by clause but not in your Select statement.

Further Explanation

Further to prove my point the following is the order in which SQL directives get executed.

1. FROM clause

2. WHERE clause

3. GROUP BY clause

4. HAVING clause

5. SELECT clause

6. ORDER BY clause

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

When used with alias the Ambiguity is resolved and you get no more errors.

Answer 2

That's because you are using the * wildcard which returns all rows. Change your query to...

Select * from business_unit order by business_unit_id desc;

And by the way, you can actually "cheat" sql server by using aliases

Answer 3

You're getting the error Ambiguous column name 'business_unit_id' because of order by clause. sql server allows to include two column with same name included in a query (without join).

So order by clause is not able to identify which column to use while sorting because it's able to see two columns with same name.

Answer 4

@M.Ali's answer explains the problem well one work around is to alias just the column, no need to alias the entire table:

select 
    business_unit_id bid,* 
from 
    business_unit 
order by 
    business_unit_id desc