Ambiguous column error on single table

I have the following code that is generated by an unsupported app

SELECT ID, TYPE, ID FROM my_table t1 ORDER BY TYPE, ID

I can't change this, now it used to work fine on SQL2008R2 or below on DB's in compatability mode 80 but due to infrastructure/licensing changes we require to move to a more modern version of SQL which no longer allows for mode 80 and now generates this error:


Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'ID'.

I'm aware it's because of the duplicated reference to the ID column but I can't alter what the app generates as TSQL, is there anything I can do at the DB\server level to have this execute without error?

PS: Yes we are working to replace this app

It's not precisely because there are duplicate entries in the SELECT list, it's because the ORDER BY mentions a column that is listed twice in the SELECT list, and SQL Server doesn't know which one you mean (think about the case where you could say SELECT ID, ID = 1-ID..., where which one you mean is important).

The right fix is (in order of my personal preference):

• remove the second instance of ID

• give the second instance of ID a different alias (like RedundantID)

• use ordinal - ORDER BY 1 (would work, but definitely not recommended)

If you can't fix the code in the app, and can't get support from the vendor, I'm not sure there's much else you can do except move back to the copy of the database running on a version that can run in the ancient compatibility level required to support this syntax.

StackExchange Database Administrators Q#239451, answer score: 4