title: Convert T-SQL Cross Apply to Oracle
body: <p>I'm looking to convert this SQL Server (T-SQL) query that uses a cross apply to Oracle 11g. Oracle does not support Cross Apply until 12g, so I have to find a work-around. The idea behind the query is for each Tab.Name that = 'Foobar', I need find the previous row's name with the same ID ordered by Tab.Date. (This table contains multiple rows for 1 ID with different Name and Date).</p>
  
  <p>Here is the T-SQL code:</p>
  
  <pre><code>SELECT DISTINCT t1.ID
                  t1.Name, 
                  t1.Date, 
                  t2.Date as 'PreviousDate',
                  t2.Name as 'PreviousName'
  FROM  Tab t1 
                 OUTER apply (SELECT TOP 1 t2.Date, 
                                           t2.Name 
                              FROM  Tab t2 
                              WHERE  t1.Id = t2.Id 
  
                              ORDER BY t2.Date DESC) t2 
  WHERE  t1.Name = 'Foobar' )
  </code></pre>
  
  <p>Technically, I was able to recreate this same functionality in Oracle using LEFT JOIN and LAG() function:</p>
  
  <pre><code>SELECT DISTINCT t1.ID
              t1.Name, 
              t1.Date, 
              t2.PreviousDate as PreviousDate,
              t2.PreviousName as PreviousName
  FROM   Tab t1  
            LEFT JOIN (
                  SELECT ID,
                  LAG(Name) OVER (PARTITION BY ID ORDER BY PreviousDate) as PreviousName,
                  LAG(Date) OVER (PARTITION BY ID ORDER BY PreviousDate) as PreviousDate
                  FROM Tab) t2 ON t2.ID = t1.ID 
  WHERE  t1.Name = 'Foobar' 
  </code></pre>
  
  <p>The issue is the order it executes the Oracle query. It will pull back ALL rows from Tab, order them (because of the LAG function), then it will filter them down using the ON statement when it joins it to the main query. That table has millions of records, so doing that for EACH ID is not feasible. Basically, I want to change the order of operations in the sub-query to just pull back rows for a single ID, sort those rows to find the previous, and join that. Any ideas on how to tweak it?</p>
  
  <p>TL;DR 
  <strong>SQL Server:</strong> filters, orders, joins
  <strong>Oracle:</strong> orders, filters, joins</p>