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');

SELECT ABC, Level, IsValid, MAX(Level) OVER(PARTITION BY ABC ORDER BY ABC) MaxOfLevel FROM Test