Use ROW_NUMBER() alias in WHERE [duplicate]

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

Answer 1

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.

Answer 2

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;