title: Trouble writing a query to select one row per "date", given certain conditions
body: <p>I am having trouble writing a query to select one row per "date", given certain conditions. My table has this structure:</p>
  
  <pre>
  ID  date       expiration    callput    iv     delta
  1   1/1/2009   1/20/2009     C          0.4    0.61
  2   1/1/2009   1/20/2009     C          0.3    0.51
  3   1/1/2009   2/20/2009     C          0.2    0.41
  </pre>
  
  <p>I would like to write a query with the following characteristics:</p>
  
  <ul>
  <li>For each row, calculate the "days", i.e. the expiration date minus the date. For instance, for row one, the "days" is 19  (1/20 minus 1/1)</li>
  <li>The result set should only have rows with a "days" of between 15 and 50</li>
  <li>The "callput" value must be "C"</li>
  <li>For each date, show only one row. That row should have the following characteristics:
  
  <ul>
  <li>The delta should be greater than 0.5</li>
  <li>The delta should be the smallest number greater than 0.5</li>
  <li>If there are two rows, the row with the lower days should be selected</li>
  </ul></li>
  </ul>
  
  <p>Here is 'days' for the sample data above:</p>
  
  <pre>
  ID  date       expiration    days    callput    iv     delta
  1   1/1/2009   1/20/2009     19      C          0.4    0.61
  2   1/1/2009   1/20/2009     19      C          0.3    0.51
  3   1/1/2009   2/20/2009     50      C          0.2    0.41
  </pre>
  
  <p>For my sample dataset, the answer should be row 2, because row 2's "delta" is above 0.5, row 2's delta of 0.51 is closer to 0.5 than row 1's 0.61, and row 2's "days" of 19 is less than row 3's "days" of 50.</p>
  
  <p>This is the query I've written so far:</p>
  
  <pre><code>SELECT date, Min(delta) AS MaxOfdelta, [expiration]-[date] AS days
  FROM RAWDATA
  WHERE (((delta)&gt;0.5) AND ((callput)="C") AND (([expiration]-[date])&gt;=15 And ([expiration]-[date])&lt;=50))
  GROUP BY date, [expiration]-[date]
  ORDER BY date;
  </code></pre>
  
  <p>This works somewhat, but sometimes, there are multiple rows for one date, because two rows on a given day can have a "days" between 15 and 50. I can't get my query to obey the rule "If there are two rows, the row with the lower days should be selected". I would also like the "iv" value for that row to be present in my query result set.</p>
  
  <p>I happen to be using Microsoft Access, but syntax for any SQL engine would be appreciated! :-)</p>