CODEWITHSUNDEEP
sqlnetezza
Michal Palko
Michal Palko

Reputation: 661

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

Upvotes: 2

Views: 74

Answers (3)

Sam
Sam

Reputation: 162

Try using a subquery in your IIF:

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Try using MAX as an analytic function:

WITH cte AS (
    SELECT year, value, MAX(year) OVER () AS max_year
    FROM yourTable
)

SELECT
    year,
    value,
    IIF(year = max_year, 'CY', 'PY') AS "CY/PY"
FROM cte;

Upvotes: 1

Related Questions