I ran into a SQL oddity today in MS SQL 2005 (and likely all prior versions) - or at least I think it's an oddity.
It revolves around case statements and column aliases, and for some reason you can't use column alias in a case statement. At least neither in the select nor order by clause.

For example:

SELECT myColumn AS C
FROM myTable
ORDER BY C 

This works perfetly well.

Now spice it up with a case (for what ever reason)

SELECT myCoulmn AS C
FROM myTable
ORDER BY
 CASE myCondition
 WHEN myValue THEN C
END 

This for some reason fails because it can't find column C......

I've yet to find the actual reason for why this happens, but that looks odd to me, and it is quite limiting at times.