SQLTeam.com | Weblogs | Forums

Filter data in same column

sql2012

#1

0
Dears i have table that contains the following columns(right picture side) and want to filter it to be like left side: ================================================================================

inout

as you see with condition if charcater repeated select highest ID.


#2

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
;

#4

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:

inout


#5

Change

where try_cast([type] as int) is null

to

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

#6

Sorry, But i guess you didn't understand me, please refer below picture, the relation depends on Level column, Major have subs.

inout


#7

I guess you didn't try!


#8

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