Is ordering done by actual column or alias?

Question

Let's say I have a simple query like this:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by grade  

It seems that order by grade is being ordered by the alias grade instead of the actual column grade; at least that's what the result shows.

Is this correct?

Since I can't change the columns that are included in the result, is the solution to add an alias to the actual column? Something like this?

select 
    grade as 'grade2', 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade,
    count(*) as 'count'
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup,grade  
order by grade2

Answer 1

If you prefix the column name by its table name (or an alias given to the table in the FROM clause) in the ORDER BY clause, then it will use the column, not the expression computed in the SELECT clause and given the same name as the column.

So this should sort using the original grade column:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl 
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by table_empl.grade

Or:

select 
    subgroup,
    subgroup + ' (' + cast(grade as varchar(1)) + 'G)' as grade, 
    count(*) as 'count' 
From table_empl t
where year(EnterDate) = year(getdate())  
group by subgroup, grade  
order by t.grade