sql server - Sql find Row with longest String and delete the Rest -
i working on table approx. 7.5mio rows , 16 columns. 1 of rows internal identifier (let's call id) use @ university. column contains string.
so, id not unique index row, possible 1 identifier appears more once in table - difference between 2 rows being string.
i need find rows id , keep 1 longest string , deleting every other row original table. unfortunately more of sql novice, , stuck @ point. if help, nice.
take @ sample:
select * #sample (values (1, 'a'), (1,'long a'), (2,'b'), (2,'long b'), (2,'bb') ) t(id,txt) delete s ( select *, row_number() on (partition id order len(txt) desc) rn #sample) s rn!=1 select * #sample
results:
id txt -- ------ 1 long 2 long b
Comments
Post a Comment