title: SQL Select row from table with some minimum value from another table
body: <p>I suppose this is not so hard but I can not get it.</p>
  
  <p>For example I have table <code>T1</code>:</p>
  
  <pre><code>ID    
  -----
  1000
  1001
  </code></pre>
  
  <p>And I have table <code>T2</code>:</p>
  
  <pre><code>ID      GROUP  DATE
  --------------------------
  1000    ADSL   2.2.2012
  1000    null   3.2.2012
  1000    NOC    4.2.2012
  1001    NOC    5.2.2012
  1001    null   6.2.2012
  1001    TV    7.2.2012
  </code></pre>
  
  <p>I want to select from <code>T1</code> only the row that has as <code>GROUP</code> value <code>NOC</code> from <code>T2</code> but only if <code>NOC</code> group is for the minimum <code>DATE</code> value in <code>T2</code>.</p>
  
  <p>So my result in this case would be only <code>1001</code> because for its minimum <code>DATE</code> 5.2.2012 Group is <code>NOC</code>!</p>
  
  <p>I do not want any joins and I can not use default values for IDs (<code>where id=1000 or id=1001</code>) because this is just example of some big table.</p>
  
  <p>Important also is that I can not use <code>t1.id = t2.id</code> because in some application where I am using this I can not write the whole SQL expression but only partial. I can only use id.</p>
  
  <p>I tried something like:</p>
  
  <pre><code>select id 
  from t1 
  where
     id in (select id from t2 
            where group = 'NOC' 
            and date in (select min(date) from t2 
                         where id in (select id from t1) 
                        )
           )
  </code></pre>
  
  <p>But this does not work.</p>
  
  <p>I know it seems little confusing but I really can't use <code>where t1.id = t2.id</code></p>
  
  <p>Thanks</p>