Clever Geek Handbook

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

Query, for example "Query-1":

select THIS_FIELD, THAT_FIELD, THAT_COUNT 
    from THE_TABLE 
    order by THIS_FIELD

works fine when THIS_FIELD

has a type String

. however, "Query-2" is below:

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

gives me an error Ambiguous column name

to occur THIS_FIELD

in a select clause when it THIS_FIELD

is of type String

and works fine when THIS_FIELD

here is an integer.

How can I get around this?

TIA.

+3


source to share


2 answers


It's just to make it work. It's not relational.

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


This will work and be ordered by ordinal position.

+2


source


If the query is written the way it is possible, you just need the alias on the table or the prefix of 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
+7


source






More articles:


All Articles