5

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).

Here is the T-SQL 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' )

Technically, I was able to recreate this same functionality in Oracle using LEFT JOIN and LAG() function:

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'

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?

TL;DR SQL Server: filters, orders, joins Oracle: orders, filters, joins

Brett
  • 51
  • 4
  • 1
    Your inner query says `t1.Date = t2.Date` and then `order by t2.Date`. So essentially it is picking a random row. – Andomar Feb 23 '15 at 19:54
  • @Andomar That sounds interesting, could you please elaborate on why that is? – Wjdavis5 Feb 23 '15 at 20:13
  • @Wjdavis5: well, if you first demand that all dates equal a certain value (say `t1.Date`), and then sort on that value, the sorting is not going to do much. – Andomar Feb 23 '15 at 20:20
  • @Andomar Thanks for pointing that out, it should not be filtering on t1.Date = t2.Date. I've updated the query now. – Brett Feb 23 '15 at 20:44
  • 2
    MS SQL Server's `APPLY` becomes a `LATERAL` in Oracle. I'm not sure at what revision that is introduced, however. In the most recent version, Oracle does support `APPLY` as well, I believe. – Bacon Bits Feb 23 '15 at 20:49

1 Answers1

0

You can look for the latest row per (id) group with row_number():

select  *
from    tab t1
left join
        (
        select  row_number() over (
                    partition by id
                    order by Date desc) as rn
        ,       *
        from    t2
        ) t2
on      t1.id = t2.id
        and t2.rn = 1 -- Latest row per id
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Not quite right. I'm looking to get the previous row (ordered by date) for Name = 'Foobar'. The name can have any date, so it's not necessarily the earliest row for the ID. – Brett Feb 24 '15 at 14:39