I am trying to process data within the same table.

Input:
Table

id sort value   
1   1     1   
2   1     8    
3   2     0    
4   1     2    

What I want to achieve is obtain for each id, the first encountered value for all value equal to its sort, and this ordered by id.

Output

Table

id sort value new  
1   1     1    1    
2   1     8    1      
3   2     0    0      
4   1     2    1   

I tried to self join the table, but I constantly get relation not found. I tried with a case statement but I don't see how can I connect to the same table, I get the same error, relation not found.

CodePudding user response：

The beauty of SQL is that many requirements (yours included) can be verbosely described in very similar way they are finally coded:

with t(id, sort, value   ) as (values
(1,   1,     1),   
(2,   1,     8),    
(3,   2,     0),    
(4,   1,     2)
)
select t.*
     , first_value(value) over (partition by sort order by id) as "new"
from t
order by id

fiddle