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))
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
The APPLY operator version is doing a "TOP 1 per group" and the LEFT JOIN version is doing a "TOP 1 from the table expression"...
They are very different.
Side note... Doing a "TOP n" without an ORDER BY clause can lead to unpredictable results as there is no guarantee which row(s) will be selected as the top.
CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
) ;
INSERT INTO #temp
VALUES ('61030203647','20160427','20160427','Re-Activattion', 'COMP');
INSERT INTO #temp
VALUES('61030203647','20160425','20160426','Re-Activattion', 'N-CO');
;WITH src AS (
SELECT Identifier , CreatedDate, CompletedDate, SN_Type, SN_Status,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate , CompletedDate) 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.SN_Status='COMP' and s2.rn>=s.rn ) d(rnGrp))
Select *
from grouped g2
DOESN'T:
;WITH src AS (
SELECT Identifier , CreatedDate, CompletedDate, SN_Type, SN_Status,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate , CompletedDate) 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.Identifier=x.Identifier and x.SN_Status='COMP' and x.rn>=s.rn )
Select *
from grouped g2
CREATE TABLE #temp
(
Identifier varchar(20)NOT NULL
,CreatedDate DATETIME NOT NULL
,CompletedDate DATETIME NOT NULL
,SN_Type varchar(20) NOT NULL
,SN_Status varchar(20) NOT NULL
) ;
;WITH src AS (
SELECT Identifier , CreatedDate, CompletedDate, SN_Type, SN_Status,
ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY CreatedDate , CompletedDate) AS rn
FROM #temp
)
,grouped as (
Select s.* ,x.rnGrp from src s
left outer join (select top 1 rn,rn rnGrp,IDS,STATUSS from src s2) x
on s.Identifier=x.Identifier and x.SN_Status='COMP' and x.rn>=s.rn )
Select *
from grouped g2