I like Sean's solution and got to wondering whether a subquery would work as well:

SELECT t.*, isnull((

select top 1 ItemCode as ItemCode

from #tTestTB t2

where t2.TreeType = 'S' and t2.VisOrder < t.VisOrder

order by VisOrder desc

), '') as ItemCode

FROM #tTestTB t

So I tried a small test harness:

IF OBJECT_ID('TempDB..#tTestTB') IS NOT NULL

DROP TABLE #tTestTB

CREATE TABLE #tTestTB(

[VisOrder] [Int] IDENTITY (0,1) NOT NULL,

[ItemCode] [varchar](25) NULL,

[TreeType] [varchar] (1) NULL

)

SET NOCOUNT ON

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3922U-1ARA','N')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'MULTI-DOCK-BUNDLE1+','S')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3934U-1PRP','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3922U-1ARA','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401-10pk+','S')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'MULTI-DOCK-BUNDLE3+','S')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3934U-1PRP','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3956U-1PRP','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3922U-1ARA','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA1495U-1TWC','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PA3945U-1EAB','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401-2PK+','S')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( 'PL10436401','I')

INSERT INTO #tTestTB ( ItemCode,TreeType) VALUES ( '920-003594','N')

;WITH Tally(n) AS (

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

FROM sys.all_columns a CROSS JOIN sys.all_columns b)

INSERT INTO #tTestTB

SELECT ItemCode + '-' + CAST(n AS VARCHAR(7))

,TreeType

FROM #tTestTB

CROSS APPLY Tally

DECLARE @Other VARCHAR(25)

PRINT 'Subquery'

SET STATISTICS TIME ON

SELECT @Other=isnull((

select top 1 ItemCode as ItemCode

from #tTestTB t2

where t2.TreeType = 'S' and t2.VisOrder < t.VisOrder

order by VisOrder desc

), '') --as ItemCode

FROM #tTestTB t

SET STATISTICS TIME OFF

PRINT 'Outer Apply'

SET STATISTICS TIME ON

SELECT @Other=isnull(x.ItemCode, '') --as ItemCode

FROM #tTestTB t

outer apply(

select top 1 ItemCode as ItemCode

from #tTestTB t2

where t2.TreeType = 'S' and t2.VisOrder < t.VisOrder

order by VisOrder desc) x

SET STATISTICS TIME OFF

DROP TABLE #tTestTB

And got these results:

Subquery

SQL Server Execution Times:

CPU time = 17972 ms, elapsed time = 18181 ms.

Outer Apply

SQL Server Execution Times:

CPU time = 18330 ms, elapsed time = 18699 ms.

Over many runs, I think such close results would probably go back and forth. There is only a slight difference in the execution plans.

Obviously I have too much time on my hands late this Friday afternoon. 😛