How do I join the first row of a subquery?

Question

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?

Select i.[Invoice Number],
       c.[Carrier Name]
From Invoice i
    Left Join Carriers c on i.[InvoiceKey] = c.[InvoiceKey]
Where -- what?

I guess semantically speaking, what I'm looking for something akin to the concept of Top 1 c.CarrierName Group by InvoiceKey (or what would be the concept of that if that were possible in T-SQL.)

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?

Edit: 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.

Answer 1

Provided that Carriers has a PRIMARY KEY called id:

SELECT  i.[Invoice Number],
        c.[Carrier Name]
FROM    Invoice i
JOIN    Carriers c
ON      c.id = 
        (
        SELECT  TOP 1 ID
        FROM    Carriers ci
        WHERE   ci.InvoiceKey = i.InvoiceKey
        ORDER BY
                id -- or whatever
        )