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
        )

Answer 2

This works for me:

select ir.[Invoice Number], c.[Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number], i.InvoiceKey
    from Invoice i) AS ir
left join Carriers c
on ir.InvoiceKey = c.InvoiceKey
where RowNumber = 1
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
    from Invoice i) AS ir
where RowNumber > 1

or

select TOP 1 i.[Invoice Number], c.[Carrier Name]
from Invoice i
left join Carriers c
on i.InvoiceKey = c.InvoiceKey
union all
select ir.[Invoice Number], NULL as [Carrier Name]
from 
    (select ROW_NUMBER() over (order by i.[Invoice Number] asc) AS RowNumber, i.[Invoice Number]
    from Invoice i) AS ir
where RowNumber > 1

Answer 3

This is how I would do it, using a slightly different syntax than yours (MySQL style), but I guess you could apply it to your solution as well:

SELECT i.invoiceNumber, c.carrierName
FROM Invoice as i
LEFT JOIN Carriers as c ON (c.id = (SELECT id FROM Carriers WHERE invoiceKey = i.invoiceKey ORDER BY id LIMIT 1))

This will take all records from Invoice, and join it with one (or zero) record from Carriers, specifically the record which has the same invoiceKey and only the first one.

As long as you have an index on Carriers.invoiceKey the performance of this query should be acceptable.

Sebastian

Answer 4

;with cteRowNumber as (
    select c.InvoiceKey, c.[Carrier Name], ROW_NUMBER() over (partition by c.InvoiceKey order by c.[Carrier Name]) as RowNum
        from Carriers c
)
select i.[Invoice Number],
       rn.[Carrier Name]
    from Invoice i
        left join cteRowNumber rn
            on i.InvoiceKey = rn.InvoiceKey
                and rn.RowNum = 1