Two columns condition in different rows in PostgreSQL
try:
select *, case when value1>=65 and min(value2) over (partition by site_id, sector_id)<25 then 'LOW_LOAD_CELL' end cell_status from your_table
I think the logic you actually want is:
select t.*, (case when max(value1) over (partition by site_it, sector_id) >= 65 and value2 < 25 then 'LOW_LOAD_CELL' end) as cell_status from t ;This conforms to your data -- if any row for a sector/site combination has value1 of 65 or over, then that cell is a low load cell when its value2 is less than 25.
You can try this:
select t.*, case when t2.cnt > 0 and value2 < 25 then 'LOW_LOAD_CELL' end cell_statusfrom mytable as t left join( select site_id, sector_id, count(*) cnt from mytable where value1 >= 65 group by site_id, sector_id ) as t2on t.site_id = t2.site_id and t.sector_id = t2.sector_id