title: SQL equivalent of Excel's approximate VLOOKUP (first relevant value) without nested query
body: <p>I am trying to find an efficient way to select the first relevant value from an existing table, as Excel's <code>VLOOKUP( , , , TRUE)</code> function would. Here is what I have, but if <code>@tableWithData</code> is very large compared to <code>@requiredDates</code>, this code can be very inefficient. I feel like I am missing something. Is there a better way of writing this:</p>
  
  <pre><code>DECLARE @requiredDates TABLE
  (requiredDate datetime)
  INSERT INTO @requiredDates VALUES ('2014-01-01');
  INSERT INTO @requiredDates VALUES ('2014-01-15');
  INSERT INTO @requiredDates VALUES ('2014-02-01');
  INSERT INTO @requiredDates VALUES ('2014-02-15');
  
  DECLARE @tableWithData TABLE
  (respectiveDate datetime,
  associatedValue int
  )
  INSERT INTO @tableWithData VALUES ('2014-01-01', 1);
  INSERT INTO @tableWithData VALUES ('2014-02-01', 2);
  
  SELECT
      lookupTable.requiredDate,
      dataTable.associatedValue
      FROM @tableWithData as dataTable RIGHT JOIN
      (
      /*Create table which maps the requiredDates -&gt; maxDate highest available date */
      SELECT
          dates.requiredDate,
          MAX(data.respectiveDate) as maxDate/*,
          data.associatedValue*/
          FROM @requiredDates as dates JOIN @tableWithData as data
          ON dates.requiredDate &gt;= data.respectiveDate
          GROUP BY dates.requiredDate
      ) as lookupTable
      on lookupTable.maxDate = dataTable.respectiveDate
  </code></pre>
  
  <p>Note: I am using MS Server 2005, but would also appreciate a more generic SQL implementation, if there is one.</p>