why can't you use an alias in a where clause?

why doesn't this work?

select grade as rx from student_work
where rx > 23;

I get this error
Msg 207, Level 16, State 1, Line 1
Invalid column name 'rx'.

I just told it what rx is.   I know there is no reason to do this in this query but I need to know why it wont work because it also doesn't work here:

select sw.*,
(
      select COUNT(*)
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
      
) as rx
from student_work sw

where rx = 1


vrosas_03Asked:
Who is Participating?
 
LowfatspreadCommented:
you can only reference objects in clauses as they are  defined in the scope of the from clause at the same level...
or in a correlated subquery from the parent level.

select x.*
from (
select sw.*,
(
      select COUNT(*) as K
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
     
) as rx
from student_work sw
) as x
where x.rx = 1



0
 
Lee SavidgeCommented:
WHERE clauses are actually evaluated before the select statement is executed so logically speaking it makes no sense to be able to use an alias in the where clause.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
jonaskaCommented:
You should try:
select *
from student_work sw
join
(
      select COUNT(*) AS rx
      from student_work sw2
      where sw.student_name = sw2.student_name and sw2.grade >= sw.grade
      
) AS sw2 ON sw.student_name = sw2.student_name
where rx = 1

Open in new window

0
 
DavidMorrisonCommented:
As the others have eluded to it is to do with the compilation order of your query. In SQL Server column aliases are the last thing to be applied / complied after everything else has been done so it doesn't know what your alias is whilst compiling / parsing the query.

Also as an aside, inline sub queries as you have done in your bottom example perform really badly in sql server, consider doing this a different way


Thanks

Dave


0
 
vrosas_03Author Commented:
I guess unlike procedural languages what is inside the parentheses is available outside.  And what is outside is unavailable inside.    This is because the from clause comprises a set.  You can't grab out of a set what hasn't been defined inside the set.  

  If a syntax doesn't work it is probably because it introduces ambiguity into the statement.

I'm just trying to get to where I can read and write something besides basic queries.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.