SQL Server -- order by clause is dependent on (field-list, type) pair

Question

The query, say "Query-1":

select THIS_FIELD, THAT_FIELD, THAT_COUNT 
    from THE_TABLE 
    order by THIS_FIELD

is running fine when THIS_FIELD is of type String. however, "Query-2" below:

select THIS_FIELD, THAT_FIELD, THAT_COUNT, * 
    from THE_TABLE 
    order by THIS_FIELD

isGiving me an Ambiguous column name error to the occurrence of THIS_FIELD in the select clause when THIS_FIELD is of type String, and is running OK when THIS_FIELD here is an integer.

How can i get around this?

TIA.

Answer 1

This is just to make it work. This is non relational.

select THIS_FIELD, THAT_FIELD, THAT_COUNT, * 
from THE_TABLE 
order by 1

This will work and order by ordinal position.

Answer 2

If the query is as written its likely that you just need an alias on the_table or prefix the columns with the table names so sql knows what to load.

select ta.THIS_FIELD, ta.THAT_FIELD, ta.THAT_COUNT, ta.* 
    from THE_TABLE ta
    order by ta.THIS_FIELD