title: Sql Server double subquery
body: <p>I have a table which is kinda like an historic table... so I have data like this</p>
  
  <p><code>idA numberMov FinalDate</code>   <br>
  <code>1     10      20090209</code>     <br>
  <code>2     14      20090304</code>    <br>
  <code>1     12      20090304</code>    <br>
  <code>3     54      20080508</code>    <br>
  <code>4     42        20090510</code>   <br>
  <code>...   ...     ....</code></p>
  
  <p>I need to retrieve the numberMov based on the newest finalDate from each idA so I use this</p>
  
  <p><code>select a.numberMov from (select idA, max(finalDate) maxDate from table1 group by idA) as b inner join table1 a on a.idA=b.idA and a.finalDate = b.maxDate</code></p>
  
  <p>Now I have another query like this</p>
  
  <p><code>select m fields from n tables where n5.numberMov in ("insert first query here")</code></p>
  
  <p>I feel like there is a better solution but can't think of any, I really dont like having two subqueries in there.</p>
  
  <p>Any suggestions?</p>