Skip to main content

Posts

Showing posts with the label row_number

How to find distinct from number of list in string

How to find distinct from number of list in string in SQL Solution: SELECT LTRIM(H,',') OUTPUT FROM (SELECT sys_connect_by_path(K,',') H, L val FROM (SELECT K, ROW_NUMBER() OVER (ORDER BY K) L FROM (SELECT UNIQUE TO_NUMBER(REPLACE(SUBSTR(G,1,INSTR(G,',',1)),',','')) K FROM (SELECT B, J, A, (LTRIM(SUBSTR(A,B,J),',')) G FROM ( SELECT DISTINCT b, LEAD(b)over (order by b ASC) j, A FROM (SELECT instr(a,',',level)b, A FROM (SELECT ('1,2,0,2,432,445,3,3,-1,10099,-2,0.32,0.3432,.3432,4,4,5,6,0.32,-2,432,32,21029,10099,3209839') ||',' a FROM dual ) CONNECT BY level <=LENGTH(A)+1 ORDER BY b ) ) WHERE B<>J ORDER BY B ) ORDER BY 1 ) ORDER BY K ) WHERE connect_by_isleaf=1 START WITH L =1 CONNECT BY L = prior L+1 );