Reference postgresql query column in other column

Question

Given I have table with some data... I have a query which is used to retrieve a data with complex case clause...

 SELECT row_number() over (...) as num, ... as field 1, 
    case ... as field2 
    FROM ...

Now I would like to add more complex logic, like

 SELECT row_number() over (...) as num, ... as field 1, 
    field2 * num as field2 
    FROM ...

Basically, if I type row_num + 1 as field2 Postgres returns me

ERROR: column "row_num" does not exist

How I can reference on field of query inside this query?

Asif · Accepted Answer · 2012-06-26 12:31:51Z

7

make a outer table and select field 2 in that table

Select *, num+1 as field2
from
(
   SELECT row_number() over (...) as num, ... as field1 FROM ...
) t

user330315

answered Jun 26, 2012 at 11:08

Asif

2,67721 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

vyegorov · Accepted Answer · 2012-06-26 14:45:51Z

0

SELECT row_number() over (...) as num,
       1 + row_number() over (...) as field2,
       ... as field3,
  FROM ...

answered Jun 26, 2012 at 14:45

vyegorov

23k7 gold badges61 silver badges76 bronze badges