CODEWITHSUNDEEP
sqlpostgresql
Vestel
Vestel

Reputation: 1025

Reference postgresql query column in other column

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?

Upvotes: 6

Views: 3859

Answers (2)

vyegorov
vyegorov

Reputation: 22885

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

Upvotes: 0

Asif
Asif

Reputation: 2677

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

Upvotes: 7

Related Questions