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

The column aliases will take precedence over actual columns in case of any ambiguity. I unfortunately cannot find definitive proof of this in Books Online. If you wanted to make sure the actual column is used, you can prefix it in the order by - `ORDER BY table_empl.grade` — GarethD, Nov 01 '16 at 14:05
weird situations like this is why I make sure that my aliases don't collide with the column names in the tables I'm selecting — DForck42, Nov 01 '16 at 14:30

Damien_The_Unbeliever · Accepted Answer · 2016-11-01T14:45:45.250

4

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

edited Nov 01 '16 at 14:45

answered Nov 01 '16 at 14:36

Damien_The_Unbeliever

234,701
27
340
448

Thanks for the post, it makes sense. It's incredible, but it never passed through my mind to prefix the column name. – fdkgfosfskjdlsjdlkfsf Nov 01 '16 at 14:44
@rbhatup - realized I'd screwed up the examples and changed it in `GROUP BY` rather than `ORDER BY`, and have now corrected. – Damien_The_Unbeliever Nov 01 '16 at 14:46

Sir Henry · Answer 2 · 2016-11-01T14:41:48.960

2

Instruction Order By runs after all instructions, even Select. And in this case it's correct to take alias instead actual column.

The clauses are processed in the following order:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

You can use name(Alias) of table to specify table column

edited Nov 01 '16 at 14:41

answered Nov 01 '16 at 14:23

Sir Henry

161
7

score 0 · Answer 3 · answered Nov 01 '16 at 14:11

A very good question. Apparently, the official documentation does not provide a direct answer to it. However, one can imply the observed behaviour from the following fact: the difference between column alias and column is that the latter can be prefixed with its parent table name (alias), whereas the former cannot.

Since you didn't specify the table name in the ORDER BY clause, the column alias takes root.

Is ordering done by actual column or alias?

3 Answers3