Use ROW_NUMBER() alias in WHERE

Question

In Postgresql 9.1+ I'm trying to filter a result set in the WHERE clause using a ROW_NUMBER() aliased field. This query works fine:

SELECT inv.client_pk, 
       inv.invoice_pk, inv.contract_pk, 
       ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) as row_number 
FROM controllr.client as cli 
  LEFT JOIN controllr.invoice as inv ON inv.client_pk = cli.client_pk                              
WHERE client_status != 3;

But when I add the "row_number" in WHERE:

SELECT inv.client_pk, 
       inv.invoice_pk, inv.contract_pk, 
       ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) as row_number 
FROM controllr.client as cli 
  LEFT JOIN controllr.invoice as inv ON inv.client_pk = cli.client_pk                              
WHERE client_status != 3 
  AND row_number <= 3;

It gives me an error:

column "row_number" does not exist

When the field "row_number" clearly exists as a aliased field.

What I'm doin wrong?

ps: I already tried with HAVING clause

Aliases defined in the `select` cannot be used in the `where`. Use a subquery or CTE. — Gordon Linoff, Feb 23 '18 at 21:49
@GordonLinoff How could I convert this to a subquery and keep the logic? — Pavarine, Feb 23 '18 at 21:51

Martin Navarro · Accepted Answer · 2018-06-01T19:34:05.787

Using subquery:

SELECT 
    *
FROM
    (SELECT 
         inv.client_pk, inv.invoice_pk, inv.contract_pk, 
         ROW_NUMBER() OVER (PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number 
     FROM 
         controllr.client as cli 
     LEFT JOIN 
         controllr.invoice as inv ON inv.client_pk = cli.client_pk                              
     WHERE 
         client_status != 3) AS sub
WHERE 
    row_number <= 3;

Using CTE:

WITH cte AS 
(
    SELECT 
        inv.client_pk, inv.invoice_pk, inv.contract_pk, 
        ROW_NUMBER() OVER ( PARTITION BY inv.client_pk ORDER BY inv.client_pk) AS row_number 
    FROM 
        controllr.client as cli 
    LEFT JOIN 
        controllr.invoice as inv ON inv.client_pk = cli.client_pk                              
    WHERE 
        client_status != 3
)
SELECT *
FROM cte
WHERE row_number <= 3;

The reason why you are receiving that error is because the WHERE clause is processed before the SELECT clause. Therefore, the engine is unable to see row_number as a column when trying to process the condition ... row_number <= 3 with your original query.

Also, using CTE has the same performance of using a subquery but it does improve readability.

upvoted! any difference in performance between cte or subquery, curious because why do we have 2 methods of doing the same thing — PirateApp, Jun 01 '18 at 00:45
@PirateApp Thanks for asking! I edited the post to clear that up. OP had 2 options from their question. Both are pretty much the same performance but CTE provides readability (especially when subqueries become huge). — Martin Navarro, Jun 01 '18 at 19:40
thanks for the clarification :) i'd prefer the CTE if both offer the same performance — PirateApp, Jun 02 '18 at 04:09

kc2018 · Answer 2 · 2018-02-23T22:21:17.423

Using subquery:

SELECT client_pk, invoice_pk, contract_pk
FROM
(
SELECT inv.client_pk, inv.invoice_pk, inv.contract_pk,
                     ROW_NUMBER() OVER
           ( PARTITION BY inv.client_pk
                      ORDER BY inv.client_pk) as row_number
FROM  controllr.client as cli 
LEFT JOIN  controllr.invoice as inv ON inv.client_pk = cli.client_pk 

WHERE client_status !=3  
) t                            
     WHERE row_number <= 3;

Use ROW_NUMBER() alias in WHERE

2 Answers2

Linked