create table CustomerNow you selecting list of customers
insert into Customer values('Alex','Smith','Mr.');
insert into Customer values('John','Doe','Dr.');
select c.Title + ' '+ c.Name +' '+c.LastName as Name from dbo.Customer cJust as planned!
order by Name
Here is a list of customers, perfectly sorted.
To be sure that your query has no issues you test it with SQL Code Guard.
What a pity!
You got an [MI003] "Unqualified column name" issue - you didn't qualified "Name" in ORDER BY clause. (To be precise you should have at least 2 tables in FROM clause to get this issue, it is not registered for queries with single table).
Ok, there is no big problem. To make the query flawless you simply qualifying the column:
select c.Title + ' '+ c.Name +' '+c.LastName as Name from dbo.Customer cAs after every query change you run corrected query:
order by c.Name
Wow! What happens? The same data - but different sort order!
The answer is very simple.
In first query "Name" in ORDER BY refers to alias in select list which is calculated from several data field.
In second query "Name" refers to column in Customer table.
So in reality these queries were sorted by two different fields.
What is conclusion?
Everybody lies. Even me, when suggesting you to qualify column name. You should always test advices which you received from anyone whatsoever.
Thanks to Stefan for pointing me to this interesting issue.