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

Question

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?

I think the way you are doing it is the best way. Basically what you are trying to do is [this](http://stackoverflow.com/questions/2043259/sql-server-how-to-join-to-first-row) which cannot be done with a straight join unless you can use Window functions which are dependent on the DBMS you are using. — Brad, Apr 29 '15 at 18:47
@Brad: Unfortunately what I'm trying to do can't be done with Netezza as its unsupported. Thanks for the link though... I'll check it out. — charles, Apr 29 '15 at 18:56

score 3 · Accepted Answer · edited Apr 29 '15 at 19:18

3

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

edited Apr 29 '15 at 19:18

Andy_Vulhop

4,699
3
25
34

answered Apr 29 '15 at 18:47

dnoeth

59,503
4
39
56

Quick question about the `NULLS FIRST`. Is this meant to prevent the case where there exist no rows where `a.date <= b.date`, or does it serve some other function? Based on the construction of the tables I'm actually guaranteed that condition will hold true for at least one row. – charles Apr 29 '15 at 18:58
Went ahead and approved this answer since it seems to be working, and it'll take me a few days to verify probably. That said, still wondering about the inclusion of `NULLS FIRST` if you don't mind. – charles Apr 29 '15 at 19:36
1

You don't need NULLS FIRST, simply remove it. Scalar Subqueries will return a NULL if no matching row exists, but of course it doesn't matter in this case. This was my error in reasoning. – dnoeth Apr 29 '15 at 20:29

score -1 · Answer 2 · answered Apr 29 '15 at 18:43

-1

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

answered Apr 29 '15 at 18:43

Wendy E

99
4

Nope. That's only going to select one row of tbl a. I'm wanting to append new_col to each record in tbl a. Also, this is with Netezza which uses ansi-sql, so it doesn't have the `TOP` statement. – charles Apr 29 '15 at 18:44
The query you wrote is a select, are you looking for an update? If so, is there a column in your table to hold the value you're trying to add? – Wendy E Apr 29 '15 at 18:47
Nope, not looking for an update. Trying to select a whole bunch of different tables together. This is just one part of a string of CTEs. – charles Apr 29 '15 at 18:59

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

2 Answers2