Search code examples
sqlnetezza

Max value from all values in column to new column

I need to add new column within select statement which contains max year (stored as text) from all records and then flag it as CY/PY. Only two year are always in the table.

 year    value   
 2019    value1  
 2019    value2  
 2018    value3

So far I tried this but it returns me only max from that line, not the whole column.

 select year, value, IIF(year=MAX(year), 'CY', 'PY') AS "CY/PY" from table

expected:

year    value     CY/PY
2019    value1    CY
2019    value2    CY
2018    value3    PY
Solution

  • I think you want a window function:

    select year, value,
       (case when year = MAX(year) over () then 'CY' else 'PY' end) AS "CY/PY"
from table