title: How can I join a record with the closest prior date in another table?
body: <p>I want to produce a sales report showing the price of each product at the time it was sold. The pricing is stored in a separate price history table, so I want to join with the most recent price that is prior to the sale date.</p>
  
  <p>I have an idea of how to implement the query, but the database I'm working with (Vertica) doesn't seem to support what I want to do.</p>
  
  <p>Here's a simplified version of the table structure:</p>
  
  <pre><code>Sales
  -----
  Date      Product ID
  1/1/2001  1
  2/2/2002  1
  3/3/2003  1
  
  PriceHistory
  -------------
  Date        ProductID     Price
  12/31/2000  1             1.00
  12/31/2001  1             1.01
  12/31/2002  1             1.11
  </code></pre>
  
  <p>Here's an example of the report I want to generate from the above data:</p>
  
  <pre><code>Sales Report
  ------------
  Date       ProductID     Price
  1/1/2001   1             1.00
  2/2/2002   1             1.01
  3/3/2003   1             1.11
  </code></pre>
  
  <p>Here's the SQL I've written so far:</p>
  
  <pre><code>SELECT s.date,
         s.productid,
         ph.price
  FROM Sales s,
       PriceHistory ph
  WHERE s.productid=ph.productid
    AND ph.date=
      (SELECT MAX(date)
       FROM PriceHistory
       WHERE productid=s.productid
         AND date &lt; s.date)
  </code></pre>
  
  <p>That might work on another DB platform, but Vertica gives me this error: "<strong>Non-equality correlated subquery expression is not supported</strong>". It doesn't like the <code>date &lt; s.date</code> component of my subquery.</p>
  
  <p>Is there another way to do this?</p>
  
  <p>Later, I found a workaround that did work on Vertica, using two different references to the PriceHistory table in the query:</p>
  
  <pre><code>SELECT s.date,
         s.productid,
         ph.price
  FROM Sales s
  JOIN PriceHistory ph ON ph.date &lt; s.date
  WHERE s.productid=ph.productid
    AND ph.date=
      (SELECT MAX(date)
       FROM PriceHistory ph2
       WHERE ph2.productid=s.productid
         AND ph2.date = ph.date)
  </code></pre>
  
  <p>There may be a better way to do it, if so I'd love to hear it.</p>