1

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

6

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♦

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.