0

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?

Chris Kao
  • 410
  • 4
  • 12

2 Answers2

2

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.

  1. From clause.
  2. Apply filter where clause
  3. Execute select statement.

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

Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60
0

You can use the rank expression as a constraint... like so.

SELECT *, 
 1 AS rk
FROM my_table
WHERE  RANK() OVER (PARTITION BY some_field ORDER BY value) = 1

Hope it helps.

NF Meola
  • 1
  • 2