SQLTeam.com | Weblogs | Forums

Syntax error!?

sql2008
sql2012
sql2008r2

#1

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))  

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  

Why doesn't it work with the left join?


#2

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.


#3

So how can I incorporate the top 1 per group on the left join?

I am trying not to use the outer apply.


#4

You're missing a comma between rn and rngrp:
left outer join (select top 1 rn,rnGrp from src s2) x

The side note from @Jason_A_Long are very inportant!


#5

Nope still not.

Here is some sample data:

WORKS:

   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

#6

Sorry my mistake,
Try this:
left outer join (select top 1 rn,rn rnGrp,IDS,STATUSS from src s2) x


#7

Still no success.

   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

#8

You changes query from your first one. Try this:
left outer join (select top 1 rn,rn rnGrp,Identifier,SN_Status from src s2) x


#9

That works

But doesnt seem to add the group like the outer apply does.

Instead its null

Outer apply

Left join