Imagine that you have a table of customers

create table Customer
(
Name varchar(30),
LastName varchar(30),
Title varchar(30)
);
go
insert into Customer values('Alex','Smith','Mr.');
insert into Customer values('John','Doe','Dr.');
go

Now you selecting list of customers

select    c.Title + ' '+ c.Name +' '+c.LastName as Name from dbo.Customer c
order by Name

Just as planned!
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 c
order by c.Name

As after every query change you run corrected query:

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.