title: Tricky SQL SELECT Statement
body: <p>I have a performance issue when selecting data in my project.</p>
  
  <p>There is a table with 3 columns: "id","time" and "group"</p>
  
  <ul>
  <li>The ids are just unique ids as usual.</li>
  <li>The time is the creation date of the entry.</li>
  <li>The group is there to cummulate certain entries together.</li>
  </ul>
  
  <p>So the table data may look like this:</p>
  
  <pre><code>ID | TIME      | GROUP
  ------------------------
  1  | 20090805  | A
  2  | 20090804  | A
  3  | 20090804  | B
  4  | 20090805  | B
  5  | 20090803  | A
  6  | 20090802  | B
  </code></pre>
  
  <p>...and so on.</p>
  
  <p><strong>The task is now to select the "current" entries (their ids) in each group for a given date. That is, for each group find the most recent entry for a given date.</strong></p>
  
  <p>Following preconditions apply:</p>
  
  <ul>
  <li>I do not know the different groups in advance - there may be many different ones changing over time</li>
  <li>The selection date may lie "in between" the dates of the entries in the table. Then I have to find the closest one in each group. That is, TIME is less than the selection date but the maximum of those to which this rule applies in a group.</li>
  </ul>
  
  <p>What I currently do is a multi-step process which I would like to change into single SELECT statement:</p>
  
  <ol>
  <li><code>SELECT DISTINCT group FROM table</code> to find the available groups</li>
  <li>For each group found in 1), <code>SELECT * FROM table WHERE time&lt;selectionDate AND group=loop ORDER BY time DESC</code></li>
  <li>Take the first row of each result found in 2)</li>
  </ol>
  
  <p>Obviously this is not optimal.</p>
  
  <p>So I would be very happy if some more experienced SQL expert could help me to find a solution to put these steps in a single statement.</p>
  
  <p>Thank you!</p>