Left Join vs Outer Apply - Not grouping

Transact-SQL
,
1

Hi

Can somone help me explain this.
Why does the left join not group the rnGrp column like the outer apply?
Thanks,
Danii

CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,START DATETIME NOT NULL
,ENDS DATETIME NOT NULL
,TYPE varchar(20) NOT NULL
,STAT varchar(20) NOT NULL
)
;


INSERT INTO #temp
VALUES('64074558792','20160731','20160805','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160801','20160805','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160809','20160809','Act','PART');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160810','20160810','Act','No');
INSERT INTO #temp
VALUES('64074558792','20160811','20160811','Act','Yes');
INSERT INTO #temp
VALUES('64074558792','20160812','20160814','Act','No');

-- WITH OUTER APPLY
 ;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) 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.Identifier = s2.Identifier and s2.STAT='Yes'and s2.rn>=s.rn ) d(rnGrp))

Select *
from grouped g2

-- WITH LEFT JOIN
;WITH src AS (
SELECT Identifier , START, ENDS, TYPE, STAT,              
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY START , ENDS) AS rn
FROM #temp 
)
,grouped as (
Select s.* ,x.rnGrp from src s
left outer join (select top 1 rn,rn rnGrp,Identifier,STAT from src s2) x
on  s.Identifier=x.Identifier and x.STAT='Yes' and x.rn>=s.rn ) 


Select *
from grouped g2
2

In the outer apply, you are selecting top 1 on a data set already filtered .
In the join version you are getting one record from a full data set , not filtered. And then you are joining.

3

Makes sense now that you put it that way.
Is there a work around that doesn't involve outer apply?

4

Something like this (maybe needs some small changes)

,grouped as (
Select s.* ,x.rnGrp from src s
left outer join (select  rn,rn rnGrp,Identifier,STAT from src s2 WHERE STAT='Yes') x
WHERE 
 s.Identifier=x.Identifier  
  and x.rn>=s.rn 
)
1 Like
5

Thank you! Huge help!

6

You're welcome!