Why is this syntax not valid?

Question

The following code:

SELECT JaguarStartupTime, CPU, AmountOfRam, UpdatedOn, *
FROM dbo.MachineConfiguration
WHERE ServerName = 'WashingtonDC01'
AND UpdatedOn > '11/21/2012'
ORDER BY JaguarStartupTime DESC

results in an error: Ambiguous column name 'JaguarStartupTime'.

However, removing the ORDER BY makes it work. In addition, prefixing the ORDER BY clause with the table, like below, makes it work too:

SELECT JaguarStartupTime, CPU, AmountOfRam, UpdatedOn, *
FROM dbo.MachineConfiguration
WHERE ServerName = 'WashingtonDC01'
AND UpdatedOn > '11/21/2012'
ORDER BY dbo.MachineConfiguration.JaguarStartupTime DESC

This never made sense to me. Can someone explain?

Answer 1

Because the query includes * (all columns) in the SELECT clause, which also includes the column JaguarStartupTime.

In Layman's terms: So in the retrieval of the results, the column shows up twice, and then when the server tries to apply the sort order, it's not sure which column you are referring to.

Personally, I'd change the query to eliminate the use of * in the query, even if it means listing a long list of column names. It's best practice, and it will avoid this issue.

---

As for why it works when prefixed, I found this in the MSDN documentation:

In SQL Server, qualified column names and aliases are resolved to columns listed in the FROM clause. If order_by_expression is not qualified, the value must be unique among all columns listed in the SELECT statement.

This is simply part of the DB engine's underlying mechanism for translating SQL statements into the execution plan.

Answer 2

I agree that in the case in your question ORDER BY JaguarStartupTime is not really ambiguous as both projected columns in fact refer to the same underlying column.

In the general case however there is no guarantee that this will be true. As discussed in the response to this connect item. For example in the following query it is clearly ambiguous which one would be used.

SELECT JaguarStartupTime,
       CPU AS JaguarStartupTime
FROM   dbo.MachineConfiguration
ORDER  BY JaguarStartupTime DESC 

For syntax validation purposes SQL Server does not do any analysis of column names to determine whether they are really ambiguous or not. When ordering by some_name that is present in the list of projected column names the some_name must be unique. This is as per the ANSI spec

If a <sort specification> contains a <column name>, then T shall contain exactly one column with that <column name> and the <sort specification> identifies that column.

Qualifying the column name with the table name ensures to SQL Server that you are ordering by a source column name rather than a projected column name so it allows it.

SELECT name, name 
FROM master..spt_values v1
ORDER BY v1.name

Additionally the following query is allowed.

select name, name 
FROM master..spt_values v1
ORDER BY name + ''

The reason why this is not ambiguous is because column aliases in an ORDER BY clause can only be used by themselves rather than in an expression so in the above query name can not be interpreted as an alias and must be interpreted as ordering by v1.name.