This question already has answers here:
Using an Alias column in the where clause in Postgresql (6 answers)
How to use row_number in a where clause (1 answer)
Closed yesterday.
I can get the rank alias with this query:
SELECT *,
RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table
Result:
| some_field | value | rk |
| ------------ | ------------ | ------------ |
| same | 10 | 1 |
| same | 20 | 2 |
| same | 30 | 3 |
And I tried to query with:
SELECT *,
RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table
WHERE rk = 1
I got this error message column "rk" does not exist
If I tried a subquery, it works :
SELECT *
FROM (
SELECT *,
RANK() OVER (PARTITION BY some_field ORDER BY value) AS rk
FROM my_table
) AS t
WHERE rk = 1
Result:
| some_field | value | rk |
| ------------ | ------------ | ------------ |
| same | 10 | 1 |
But my question is why can't we use just one SELECT to do so.
Is it because I use a function in my query?
2条答案
按热度按时间elcex8rz1#
是的。。你不能在where子句中使用列名。首先了解SQL执行流程。根据你的SQL语句,它将按以下顺序执行。
1.应用筛选where子句
1.执行select语句。
所以在你的sql语句中,rk列是在第三步生成的。但是你在第二步访问,这是不可能的。希望它能被清除
w1jd8yoj2#
您可以使用秩表达式作为约束条件...如下所示。
希望能有所帮助。