How to use an alias name that has RANK() OVER in a WHERE clause

Question

I have a query that has a RANK() OVER function inside it but I would like to use the results displayed on this column in the WHERE clause that follows. How do I write that as all the other questions I have looked at did not have RANK() OVER and seemed easier to do. Here is the statement:

USE SMSResults


SELECT Student_No,Result,Module_Name,Semester,Year,RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
FROM tbl_results
WHERE Student_No = '201409'

ORDER BY Year DESC

I would like to use the Rnk column in the WHERE clause

Answer 1

I would like to use the Rnk column in the WHERE clause

The Rnk is a column computed in the SELECT clause. It's not avaiable in the WHERE clause of the same level, as the logical order of execution a query is FROM -> WHERE -> SELECT.

You have to wrap the query in a subquery. You can use either a CTE (Common Table Expression):

USE SMSResults ;
go
with CTE as 
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409')
select * from CTE 
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

or a derived table:

USE SMSResults ;
go   
select * from
    ( SELECT Student_No,Result,Module_Name,Semester,Year,
             RANK() OVER (PARTITION BY Student_No ORDER BY Semester  DESC) AS Rnk
      FROM tbl_results
      WHERE Student_No = '201409') 
  AS derived_table
where rnk > 1   -- change here with whatever you want ... !!
ORDER BY Year DESC ;

As a side note for future readers - worth reading - What's the difference between a CTE and a Temp Table? by JNK♦