July 22, 2017 at 8:40 am
Hi
I am trying to utilize the below query but without using otter apply
Can someone help me out with an alternative?
Thanks,
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))
There are not results i can post which I am after, i am just trying to run the query to see if it will work on a set of data and how.
Any help would be appreciated.
July 22, 2017 at 9:24 am
not sure I understand your question.
are you saying the code errors (doesnt work)
or that it works but you are not getting the results you require?
why dont you want to use outer apply?
without any sample data/expected results it is hard to help
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 22, 2017 at 9:26 am
J Livingston SQL - Saturday, July 22, 2017 9:24 AMnot sure I understand your question.
are you saying the code errors (doesnt work)
or that it works but you are not getting the results you require?without any sample data/expected results it is hard to help
Hi
Woks fine
Was just after knowing how I could achieve the same but without using outer apply.
July 22, 2017 at 9:29 am
J Livingston SQL - Saturday, July 22, 2017 9:24 AMnot sure I understand your question.
are you saying the code errors (doesnt work)
or that it works but you are not getting the results you require?without any sample data/expected results it is hard to help
Piling on, the question is not clear, please elaborate further!
😎
You could try 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
July 22, 2017 at 9:32 am
Eirikur Eiriksson - Saturday, July 22, 2017 9:29 AMJ Livingston SQL - Saturday, July 22, 2017 9:24 AMnot sure I understand your question.
are you saying the code errors (doesnt work)
or that it works but you are not getting the results you require?without any sample data/expected results it is hard to help
Piling on, the question is not clear, please elaborate further!
😎You could try
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
Will give it a shot, let you know how it goes.
July 23, 2017 at 11:13 am
One quick note: you're using TOP 1 without an ORDER BY, so if you have multiple values for rn that meet your criteria, there's no guarantee you'll get the same one every time the query is run.
Also note that your original query and Eirikur's are not functionally equivalent.
If you could specify what you're trying to do, we'd be able to offer more help.
Cheers!
July 24, 2017 at 7:30 pm
Hi Jacob
I am going mad here.
Any idea where i am going wrong in the below:;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
Getting Incorrect syntax near ')'.
July 26, 2017 at 5:53 am
Not sure why that would give an error.
But there are a few things other than that which will give your errors.
I also checked your other post out here
https://www.sqlservercentral.com/Forums/1888430/Join-not-working-simulate-outer-apply
You can't replace the original query with a LEFT OUTER JOIN method
You can use a correlated sub query, but that's not much different than the OUTER APPLY method
;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.* ,
(select top 1 rn rnGrp from src s2 where s.IDS = s2.IDS and s2.STATUSS='COMP' and s2.rn>=s.rn ) rnGrp
from src s
)
I would prefer the OUTER APPLY method as that looks more readable to me. Not sure why you are looking for an alternative.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply