Search code examples
sqlnetezzaansi-sql

How would I write this query as a join instead of a correlated query?

So, Netezza can't use correlated subqueries in SELECT statements, which is unfortunate that I can't think of a single way to avoid this in my particular case. I was thinking about doing something with ROW_NUMBER(); however, I can't include windowing functions in a HAVING clause.

I've got the following query:

select 
    a.*
    ,(  select b.col1
        from b
        where b.ky = a.ky
            and a.date <= b.date
        order by b.date desc
        limit 1
    ) as new_col
from a

Any suggestions?

Solution

  • This should return the expected result:

    select *
from 
 (
   select 
      a.*
     ,b.col1 as b_col1
     ,row_number() 
      over (partition by a.ky
            order by b.date desc NULLS FIRST) as rn 
   from a left join b
   where b.ky = a.ky 
   and a.date <= b.date
 ) as dt
where rn = 1