0
Dears i have table that contains the following columns(right picture side) and want to filter it to be like left side: ================================================================================
as you see with condition if charcater repeated select highest ID.
Like this?
with cte
as (select id
,[type]
,row_number() over(order by id) as rn
from [main table]
where try_cast([type] as int) is null
)
select c.id
,a.[type]
,c.[type] as [value]
from cte as a
left outer join cte as b
on b.rn=a.rn+1
inner join [main table] as c
on c.id>a.id
and c.id<isnull(b.id,999999999)
order by c.id
;
Sir, i noticed that you used Cast to differntiate between characters and numbers, what if the column contains mixed data (numbers and charcaters) as per below picture:
Change
where try_cast([type] as int) is null
to
where [type] not like '[0-9]%'
Sorry, But i guess you didn't understand me, please refer below picture, the relation depends on Level column, Major have subs.
I guess you didn't try!
Oh, sorry i was using the incorrect column in condition, it works, please accept my apologize and thank you very much for your support