Join not working (simulate outer apply)

• Danii_Pink

  Right there with Babe

  Points: 778

  More actions

  July 24, 2017 at 8:48 pm

  #343765

  Hi

  Any idea where i am going wrong in the below:

  This works:

  
WITH src AS (SELECT (ID1 + ' - ' + ID2) as IDS ,
DATE1,
DATE2,
TYPES,
STATUSS,
   ROW_NUMBER() OVER(PARTITION BY (ID1 + ' - ' + ID2) ORDER BY DATE1 , DATE2) AS rn FROM #temp )
,grouped as
(Select s.* ,d.rnGrp from src s
outer apply (select top 1 rn rnGrp from src s2 where s.IDS = s2.IDS and s2.STATUSS='COMP' and s2.rn>=s.rn ) d(rnGrp))

  Select g2.IDS
from grouped g2


  This doesnt:

  
WITH src AS (SELECT (ID1 + ' - ' + ID2) as IDS ,
DATE1,
DATE2,
TYPES,
STATUSS,
  ROW_NUMBER() OVER(PARTITION BY (ID1 + ' - ' + ID2) ORDER BY DATE1 , DATE2) AS rn FROM #temp )
,grouped as
(Select s.* ,x.rnGrp from src s
left outer join (select top 1 rn rnGrp from src s2) x
on s.IDS=x.IDS and x.STATUSS='COMP' and x.rn>=s.rn)

  Select g2.IDS
from grouped g2