Column reference

Question

I am querying a database of fuel imports and exports.

This is the relevant code:

--Find the top 3 regions with the highest fuel exports

SELECT 
    exp.Country_Name AS Region, exp.Year,    
    exp.Fuel_exports_as_precentage_of_total_exports AS Fuel_exports_Prec,
    DENSE_RANK() OVER (PARTITION BY exp.Year ORDER BY exp.Fuel_exports_as_precentage_of_total_exports DESC) AS rnk
FROM 
    dbo.[Fuel_exports] exp
JOIN 
    dbo.[Countries] ctr ON exp.Country_Name = ctr.Country
WHERE 
    ctr.Region IS NULL 
    AND exp.rnk < 4

Now I want to filter the outcome to only when the last column selected(rnk) is less then 4.

How can I use WHERE on the alias (WHERE exp.rnk < 4) instead of writing the whole calculation again and make it cumbersome?

This is the error I get:

I looked for code mistakes and did not find any

Answer 1

You can't reference an alias as such. One option is a subquery or even a CTE.

Example

Select *
 From  (
        SELECT exp.Country_Name AS Region, exp.Year, exp.Fuel_exports_as_precentage_of_total_exports AS Fuel_exports_Prec,
        DENSE_RANK() OVER(PARTITION BY exp.Year ORDER BY exp.Fuel_exports_as_precentage_of_total_exports DESC) AS rnk
        FROM dbo.[Fuel_exports] exp
        JOIN dbo.[Countries] ctr
            ON exp.Country_Name = ctr.Country
        WHERE ctr.Region IS NULL 
       ) src
 Where rnk < 4

Answer 2

SQL Query follow below sequence, So we can not use Alias of "Order By" clause directly in same query:-

FROM & JOINs determine & filter rows
WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups
LIMIT filters on the remaining rows/groups 

---

You can try this:-

With CTE AS ( 
SELECT 
  exp.Country_Name AS Region, 
  exp.Year, 
  exp.Fuel_exports_as_precentage_of_total_exports AS Fuel_exports_Prec, 
  DENSE_RANK() OVER(
    PARTITION BY exp.Year 
    ORDER BY 
      exp.Fuel_exports_as_precentage_of_total_exports DESC
  ) AS rnk 
FROM 
  dbo.[Fuel_exports] exp 
  JOIN dbo.[Countries] ctr ON exp.Country_Name = ctr.Country 
WHERE 
  ctr.Region IS NULL
) 
Select 
  * 
from 
  CTE 
Where 
  rnk < 4