VisOrder	ItemCode	TreeType
0	PA3922U-1ARA	                N
1	MULTI-DOCK-BUNDLE1+  S
2	PA3934U-1PRP	                I
3	PA3922U-1ARA	                I
4	PL10436401	                I
5	PL10436401-10pk+	        S
6	PL10436401	                I
7	MULTI-DOCK-BUNDLE3+	S
8	PA3934U-1PRP	                I
9	PA3956U-1PRP	                I
10	PL10436401	                I
11	PA3922U-1ARA	                I
12	PA1495U-1TWC	        I
13	PA3945U-1EAB	                I
14	PL10436401-2PK+	        S
15	PL10436401	                I
16	920-003594	                N

IF OBJECT_ID('TempDB..#tTestTB') IS NOT NULL
DROP TABLE #tTestTB
CREATE TABLE #tTestTB(
	[VisOrder] [Int] NULL,	
	[ItemCode] [varchar](25) NULL,
	[TreeType] [varchar] (1) NULL
	) 

INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   0,'PA3922U-1ARA','N')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   1, 'MULTI-DOCK-BUNDLE1+','S')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   2, 'PA3934U-1PRP','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   3, 'PA3922U-1ARA','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   4, 'PL10436401','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   5, 'PL10436401-10pk+','S')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   6, 'PL10436401','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   7, 'MULTI-DOCK-BUNDLE3+','S')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   8, 'PA3934U-1PRP','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   9, 'PA3956U-1PRP','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   10, 'PL10436401','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   11, 'PA3922U-1ARA','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   12, 'PA1495U-1TWC','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   13, 'PA3945U-1EAB','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   14, 'PL10436401-2PK+','S')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   15, 'PL10436401','I')
INSERT INTO #tTestTB (  VisOrder,ItemCode,TreeType) VALUES (   16, '920-003594','N')

 SELECT * FROM  #tTestTB
 DROP TABLE #tTestTB

 DECLARE @ID INT = 2
 SELECT TOP 1 * FROM  #tTestTB 
 WHERE VisOrder < @ID AND TreeType = 'S' 
  AND EXISTS(SELECT * FROM #tTestTB WHERE VisOrder = @ID AND TreeType <> 'S') 
 ORDER BY VisOrder DESC

SELECT t.*, 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

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

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

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.