How to use an alias name that has RANK() OVER in a WHERE clause
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 -&gt; WHERE -&gt; 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 &gt; 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 &gt; 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♦