SQL Server Column reference

6fe3ivhb 于 2023-03-17 发布在其他

关注(0)|答案(2)|浏览(177)

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

sql-server

来源：https://stackoverflow.com/questions/75705364/column-reference

2条答案

按热度按时间

dgsult0t1#

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

赞(0）回复(0）举报 2023-03-17

c7rzv4ha2#

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

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Column reference

2条答案

相关问题

热门标签

最新问答