By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tablename (St_Num varchar(50), status varchar(3));
insert into tablename (St_Num, status) values
('128 TIMBER RUN DRIV', 'EXP'),
('128 TIMBER RUN DRI', 'EXP'),
('128 TIMBER RUN DR', 'EXP'),
('128 TIMBER RUN DRIVE', 'EXP'),
('120 TIMBER RUN DRIVE', 'EXP');
5 rows affected
with cte as (
select *,
row_number() over (order by (select null)) rn
from tablename
)
select St_Num, status from cte t
where not exists (
select 1 from cte
where
left(St_Num, 7) = left(t.St_Num, 7)
and
rn < t.rn
)
| St_Num | status |
|---|---|
| 128 TIMBER RUN DRIV | EXP |
| 120 TIMBER RUN DRIVE | EXP |