CODEWITHSUNDEEP
sql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Is ordering done by actual column or alias?

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

Upvotes: 3

Views: 385

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

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

Upvotes: 5

Sir Henry
Sir Henry

Reputation: 171

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

Upvotes: 3

Roger Wolf
Roger Wolf

Reputation: 7692

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.

Upvotes: 0

Related Questions