with TMP as ( select max(code) as code,code_seul from ( select code, substring(code,1,charindex(code,'-')-1) as code_seul from Table where code like '%-%') t1 group by code_seul ) select xxx,yyy from Table where code not like '%-%' union select T.xxx,yyy from Table T inner join TMP on T.code = TMP.code
select xxx,yyy from Table where code not like '%-%' union select T.xxx,T.yyy from Table T inner join ( select max(code) as code,code_seul from ( select code, substring(code,1,charindex(code,'-')-1) as code_seul from Table where code like '%-%') t1 group by code_seul ) as TMP on T.code = TMP.code
if exists(select name from tempdb..sysobjects where name like '#FTARTICLE%') begin drop table #FTARTICLE END; SELECT ar_ref,ar_design,commentaire_tarif,CASE ar_nomencl WHEN 0 THEN ar_punet WHEN 1 THEN ar_coutstd end as prixachat,ar_coef,famille,estim_tps_fab,ar_prixven INTO #FTARTICLE FROM OPENQUERY(SAGE100,'SELECT a.*, c1.cl_code + ''-'' + c2.cl_intitule AS famille FROM f_article a INNER JOIN f_catalogue c1 ON a.cl_no1 c1.cl_no INNER JOIN f_catalogue c2 ON a.cl_no2 c2.cl_no WHERE (c1.cl_code ''AC'' OR c1.cl_code ''FB'') AND a.ar_sommeil=0'); select * from #FTARTICLE where ar_ref not like '%-%' union select T.* from #FTARTICLE T inner join ( select max(ar_ref) as ar_ref,code_seul from ( select ar_ref, substring(ar_ref,1,charindex(ar_ref,'-')-1) as code_seul from #FTARTICLE where ar_ref like '%-%' ) t1 group by code_seul ) as t2 on T.ar_ref = t2.ar_ref
charindex('-', ar_ref)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionselect ... from Table where code not like '%-%' union select ... from Table T where exists ( select 1 where T.code = select code from ( select max(code) as code ,code_seul from ( select code, substring(code,1,charindex(code,'-')-1) as code_seul from Table where code like '%-%' ) tmp group by code_seul ) )
select xxx,yyy from Table where code not like '%-%' union select T.xxx,T.yyy from Table T , ( select max(code) as code,code_seul from ( select code, substring(code,1,charindex(code,'-')-1) as code_seul from Table where code like '%-%') t1 group by code_seul ) as TMP where T.code = TMP.code