CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Senthil
Senthil

Reputation: 101

Add a additional column with value Yes or No if the max value group by matches

I have a table lets say as below

USER Week DAY VERSION
First week 1 SUNDAY 5
Second Week 1 MONDAY 5
FIrst week 1 SUNDAY 3

Now I want to display all columns and rows and add additional column with Y against each row when the row has max(version) for that user and week and if N if the version is not max for that user and week id . How could the sql query be written for this please?

Upvotes: 0

Views: 42

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

It could be achieved with windowed functions:

SELECT *,
  CASE WHEN ROW_NUMBER() OVER(PARTITION BY USER, WEEK ORDER BY VERSION DESC) = 1 
       THEN 'Y' ELSE 'N' END
FROM tab;

Upvotes: 1

Related Questions