title: Effective SQL query to find first (by date) associated record in a 1-N relationship
body: <p>Here's the scenario: there is a Software table (PK = SoftwareID) and an associated Release table (PK = [SoftwareID,Version]). </p>
  
  <p>A release can be major or minor, release type is identified by Release.ReleaseType ('MAJ', 'MIN').</p>
  
  <p>A release is also characterized by a date: Release.ReleaseDate.</p>
  
  <p>Software is partitioned into categories, identified by Software.CategoryID.</p>
  
  <p>Problem: need an <strong>effective</strong> T-SQL query to list all software pieces of a certain category and having the first major release date falling inside a given interval, delimited by @DateFrom, @DateTo. The only columns needed in the final resultset are SoftwareID and ReleaseDate.</p>
  
  <p>This is not the real case scenario but I formulated it this way to be easier to understand. In the real case the table Release would have around 10 million records and the table Software around 1 million. I came up already with a solution but it's quite slow and I feel the experts around here might find something better.</p>
  
  <p>Here's my slow solution:</p>
  
  <pre><code>select  s.SoftwareID, min(r.ReleaseDate)
  from
      Software s inner join Release r on (s.SoftwareID = r.SoftwareID)
  where s.CategoryID = @Category
        and r.ReleaseType = 'MAJ'
  group by
      s.SoftwareID
  having
      min(r.ReleaseDate) &gt;= @DateFrom
      and min(r.ReleaseDate) &lt; @DateTo
  </code></pre>
  
  <p>Thanks.</p>