title: How do I join the first row of a subquery?
body: <p>I've got a table of invoices and a child table of related data related by key.  In particular, for each invoice, I'm interested in only the first related row from the child table.  Given that I want the one related row for every invoice key - how do I accomplish this?</p>
  
  <pre><code>Select i.[Invoice Number],
         c.[Carrier Name]
  From Invoice i
      Left Join Carriers c on i.[InvoiceKey] = c.[InvoiceKey]
  Where -- what?
  </code></pre>
  
  <p>I guess semantically speaking, what I'm looking for something akin to the concept of <code>Top 1 c.CarrierName Group by InvoiceKey</code> (or what would be the concept of that if that were possible in T-SQL.)</p>
  
  <p>I've thought about doing a left join on a subquery, but that doesn't seem very efficient.  Does anyone have any T-SQL tricks to achieve this efficiently?</p>
  
  <p><strong>Edit</strong>:  Sorry guys, I forgot to mention this is SQL Server 2000, so while I'm going to give upvotes for the current SQL Server 2005/2008 responses that will work, I can't accept them I'm afraid.</p>