title: Join top n records from child table
body: <p>I am struggling to figure out how to select only the first 4 records from a child table per record in the parent table in a master-detail relationship.</p>
  
  <p>Tables example:</p>
  
  <pre><code>Product         ProductImage
  ---------       -------------------
  Id | Name       PKeyFld1 | PKeyFld2
  ---------       -------------------
   1 | Apple             1 |        1
   2 | Banana            1 |        2
   3 | Cranberry         1 |        3
                         1 |        4
                         1 |        5
                         2 |        1
                         2 |        2
                         2 |        3
                         3 |        1
                         3 |        3
                         3 |        4
                         3 |        8
                         3 |        9
  </code></pre>
  
  <p>The primary key for ProductImage is a combination of the two shown fields. I need to get the first 4 images per product, ordered by <code>PKeyFld1, PKeyFld2</code>, which would result in:</p>
  
  <pre><code>ProductImage
  -------------------
  PKeyFld1 | PKeyFld2
  -------------------
         1 |        1
         1 |        2
         1 |        3
         1 |        4
         2 |        1
         2 |        2
         2 |        3
         3 |        1
         3 |        3
         3 |        4
         3 |        8
  </code></pre>
  
  <p>The nicest solution would be to have only one query with 1 record per product, but I can also deal with two queries; 1 for the products and 1 for the images. In C#, I can fetch them and add the image data to the model before further processing it.</p>
  
  <p>Can somebody help me with the query for the productImages? The hard part is in getting only the top 4 images per product, without limiting the whole <code>ProductImage</code> table to only 4 records. I have done this with Postgres in the past, but cannot find how to do this in SQL Server.</p>