OUTER APPLY with SELECT TOP 1, is it worth it, can be replaced ??

Question

OUTER APPLY with SELECT TOP 1, is it worth it, can be replaced ??

mario17

SSCertifiable

Points: 6079
More actions
May 29, 2014 at 11:51 am

#291748

Hi,
I see that combo in some code and wandering is can be replaced as OUTER APPLY is hard on execution plan.
What could be other biz scenarios when to use select top 1 with outer apply, I think I can be replaced with inner join with extra checking for dupls if needed.
Tx
M
SELECT
a.CompleteAddress
s1.Suburb
FROM
tblCrap_Address a
OUTER APPLY
(SELECT TOP 1 s2.Suburb FROM tblLookup_Suburb s2 where
a.CompleteAddress
LIKE '%' + s2.Suburb + '%' ORDER BY LEN(s2.Suburb) DESC) s1

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

It depends. What are your business requirements for the code?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mario17 SSCertifiable Points: 6079 More actions · Answer 2

In my case looks like we want to pick most recent item from the list for given ID,

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

The sample query you provided does nothing of what you describe. How about posting a query that you have been working on to accomplish the business requirements and then we can help provide legit options.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mario17 SSCertifiable Points: 6079 More actions · Answer 4

Here is a piece of sample code to select most recent date for order for each customer:

Tx

mario

/*

DECLARE @dd DATETIME = '2014-2-2'

SELECT * INTO #td FROM (

SELECT 100 CustID, 'Mike' Name UNION

SELECT 333 CustID, 'Nina' Name UNION

SELECT 4444 CustID, 'Alpha' Name ) b

SELECT * INTO #to FROM (

SELECT 100 CustID, 10 RefillID, DATEADD(d,-2,@dd) DateSend UNION

SELECT 100 CustID, 20 RefillID, DATEADD(d,-1,@dd) DateSend UNION

SELECT 100 CustID, 30 RefillID, DATEADD(d,-3,@dd) DateSend UNION

SELECT 4444 CustID, 77 RefillID, DATEADD(d,-3,@dd) DateSend

) b

*/

---------------------------- A is this the best?

SELECT #td.custID, #td.NAME,

o.DateSend

FROM #td

OUTER APPLY (SELECT TOP 1 DateSend FROM

#to WHERE #to.CustID = #td.CustID ORDER BY DateSend DESC) o

----------------------------

SELECT #td.custID,

#to.DateSend

FROM #td

LEFT JOIN #to ON #to.CustID = #td.CustID

AND #to.DateSend = (SELECT MAX(to2.Datesend) FROM #to to2 WHERE to2.CustID = #to.CustID)

----------------------------

SELECT * FROM (

SELECT *

,RANK () OVER (PARTITION BY CustID ORDER BY Datesend DESC) AS rr

FROM #to ) b

WHERE rr = 1

pietlinden SSC Guru Points: 63503 More actions · Answer 5

Did you compare it to a standard MAX(Date) with a GROUP BY clause?