CODEWITHSUNDEEP
sqlnetezzaansi-sql
charles
charles

Reputation: 547

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?

Upvotes: 2

Views: 166

Answers (2)

dnoeth
dnoeth

Reputation: 60482

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

Upvotes: 3

Wendy E
Wendy E

Reputation: 99

I'm not completely sure I understand your question, but is this what you're looking for?

SELECT TOP 1 a.*, b.col1 FROM a JOIN b ON a.ky = b.ky
WHERE a.date <= b.date ORDER BY b.date desc

Upvotes: -1

Related Questions