CODEWITHSUNDEEP
sqlsql-server
Shabina
Shabina

Reputation: 41

Use Rank() over a pseudo Column Name

I have a table with columns:

from which I need to perform a query that will calculate the average of two marks and rank the rows from highest average to least.

I executed the following query:

SELECT
  *,
  (SELECT AVG(c) FROM (VALUES(Marks1),(Marks2)) T (c)) AS Average,
  RANK() OVER (ORDER BY Average DESC) AS Position
from Marks;

But that gives an error:

Average is an Invalid Column Name.

How do I fix this? How do I give a query to perform Rank() over Average.

Upvotes: 0

Views: 517

Answers (2)

Thom A
Thom A

Reputation: 96013

You can't reference a column by its alias in the SELECT; the only place you can reference its alias is in the ORDER BY clause.

What you can do, however, is move the subquery to the FROM, and then you can reference the column returned in your (outer) SELECT:

SELECT M.*,--List your columns here, don't use *
       A.Average,
       RANK() OVER (ORDER BY A.Average DESC) AS Position
FROM Marks M
     CROSS APPLY(SELECT AVG(Mark) AS Average FROM (VALUES(Marks1),(Marks2)) V(Mark) ) A;

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

You should just use the average of the two marks inlined in the outer query:

SELECT *, RANK() OVER (ORDER BY (Marks1 + Marks2) / 2 DESC) AS Position
FROM Marks
ORDER BY (Marks1 + Marks2) / 2 DESC;

Upvotes: 3

Related Questions