title: Oracle select records based on a min date where min date between
body: <p>My SQL currently looks like this.</p>
  
  <pre><code>SELECT t1.field1,
         t1.field2,
         t1.field3,
         t1.field4,
         t1.field5,
         t1.field6,
         t1.field7,
         t1.field8,
         t2.field1,
         t2.field2,
         t2.field3,
         t2.field4,
         t2.field5,
         t2.field6,
         t2.field7,
         t2.field8,
         t2.field9,
         t3.field1,
         t4.field1,
         t5.field1,
         SUM(t6.field1),
         MIN(t6.THEDATE) 
  
    FROM table1 t1
      LEFT JOIN table2 t2
        ON t1.field2 = t2.sameFieldName
        LEFT JOIN table3 t3
          ON t2.field9 = t3.sameFieldName
          LEFT JOIN table4 t4
            ON t1.field2 = t4.sameFieldName
            AND t2.field1 = t4.sameFieldName
            LEFT JOIN table5 t5
              ON t4.field1 = t5.sameFieldName
              LEFT JOIN table6 t6
                ON t4.field1 = t6.sameFieldName
                AND t4.colName1 = t6.sameFieldName
  
    WHERE t6.THEDATE BETWEEN SYSDATE - 70 AND SYSDATE - 50
      AND t1.field2 = 'SUBMIT'
      AND t1.field3 LIKE 'H%'
  
    GROUP BY t1.field1,
             t1.field2,
             t1.field3,
             t1.field4,
             t1.field5,
             t1.field6,
             t1.field7,
             t1.field8,
             t2.field1,
             t2.field2,
             t2.field3,
             t2.field4,
             t2.field5,
             t2.field6,
             t2.field7,
             t2.field8,
             t2.field9,
             t3.field1,
             t4.field1,
             t5.field1;
  </code></pre>
  
  <p>The problem I have is I need to select with the condition based on the min date. But doing it this way will "show" the min date, but it will filter records based on whatever the last "theDate" value is. I know you cannot use agg functions in the where clause because the where only operates on a single record. So how can I get something that would work like this?</p>
  
  <pre><code>SELECT *, sum(somthing), min(theDate)
  FROM Table
  WHERE min(theDate) BETWEEN SYSDATE - 70 AND SYSDATE - 50
  GROUP BY &lt;&lt;&lt;ALL GROUP COLUMNS&gt;&gt;&gt;
  </code></pre>