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

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?

CodePudding user response：

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;