SQLTeam.com | Weblogs | Forums

Filter data in same column

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
            ,row_number() over(order by id) as rn
        from [main table]
       where try_cast([type] as int) is null
select c.id
      ,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
1 Like

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:



where try_cast([type] as int) is null


where [type] not like '[0-9]%'
1 Like

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!

1 Like

Oh, sorry i was using the incorrect column in condition, it works, please accept my apologize and thank you very much for your support