clear markdown feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798982 fiddles created (41851 in the last week).

CREATE TABLE Test ( ABC varchar(30), Level int, IsValid varchar(30) ); INSERT INTO Test(ABC, Level, IsValid) VALUES ('10D34AV', 0, 'No'), ('10D34AV', 1, 'No'), ('10D35AV', 0, 'No'), ('10D35AV', 1, 'No'), ('10D36AV', 0, 'Yes'), ('10D36AV', 1, 'Yes'), ('10D36AV', 2, 'Yes'), ('10D36AV', 2, 'Yes'), ('10D36AV', 2, 'Yes'), ('10D36AV', 1, 'Yes'), ('10D36AV', 2, 'Yes'), ('10D37AV', 0, 'Yes'), ('10D37AV', 1, 'Yes'), ('10D37AV', 2, 'Yes'), ('10D37AV', 2, 'Yes'), ('10D37AV', 1, 'Yes'), ('10D37AV', 2, 'Yes'), ('10D38AV', 0, 'Yes'), ('10D38AV', 1, 'Yes'), ('10D38AV', 2, 'Yes'), ('10D38AV', 2, 'Yes'), ('10D38AV', 2, 'Yes'), ('10D38AV', 1, 'Yes'), ('10D38AV', 2, 'Yes'), ('10D39AV', 0, 'No'), ('10D39AV', 1, 'No');
26 rows affected
 hidden batch(es)


SELECT ABC, Level, IsValid, MAX(Level) OVER(PARTITION BY ABC ORDER BY ABC) MaxOfLevel FROM Test
ABC Level IsValid MaxOfLevel
10D34AV 0 No 1
10D34AV 1 No 1
10D35AV 0 No 1
10D35AV 1 No 1
10D36AV 0 Yes 2
10D36AV 1 Yes 2
10D36AV 2 Yes 2
10D36AV 2 Yes 2
10D36AV 2 Yes 2
10D36AV 1 Yes 2
10D36AV 2 Yes 2
10D37AV 0 Yes 2
10D37AV 1 Yes 2
10D37AV 2 Yes 2
10D37AV 2 Yes 2
10D37AV 1 Yes 2
10D37AV 2 Yes 2
10D38AV 0 Yes 2
10D38AV 1 Yes 2
10D38AV 2 Yes 2
10D38AV 2 Yes 2
10D38AV 2 Yes 2
10D38AV 1 Yes 2
10D38AV 2 Yes 2
10D39AV 0 No 1
10D39AV 1 No 1
 hidden batch(es)