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?
Yes.. You can't use the column name in where clause. First understand, SQL execution flow. As per your sql statment, it will execute in below order.
So in your sql statment, rk column is generating at step 3. But you are accessing at step 2 which is not possible. Hope it will clear