sql server join and select top 1

Question

I have one customer table and one address table.For each customer there should be multiple address in address Table I need A stored procedure to select a customer details and his address on top from address table parameter to select customer is companyID

SELECT top 1  s.Addr1 as ShipAddress_Addr1,s.Addr2 as  ShipAddress_Addr2,s.Addr3 as ShipAddress_Addr3,
    s.Addr4 as ShipAddress_Addr4,s.Addr5 as ShipAddress_Addr5,
    s.City as ShipAddress_City,s.[state] as ShipAddress_State,s.PostalCode as ShipAddress_PostalCode,
    s.Country as ShipAddress_Country,s.Note as ShipAddress_Note ,c.CustomerID,c.[TimeCreated], c.[FullName],c.FirstName,c.LastName,c.Phone, c.Email, 
    c.BillAddress_Addr1, c.[BillAddress_Addr2],c. [BillAddress_Addr3],c. [BillAddress_Addr4],c.[BillAddress_Addr5],
     c.BillAddress_City,c.BillAddress_State,c.BillAddress_PostalCode,c.BillAddress_Country,c.BillAddress_Note
        FROM   Customer c left join [dbo].[CustomerShipToAddress] s on s.customerListID=c.CustomerID    
    WHERE c.IsActive = 1 and c.CompanyID = @CompanyID

Answer 1

You can outer apply the latest address for each customer like this:

select c.customerid, c.name, c.accno, c.txnid, ta.add1, ta.add2
from customertable c
outer apply (select top 1 a.add1, a.add2 from addresstable a where a.customerid = c.customerid order by a.addressid desc) ta

The sub-query in the outer apply with always return 0 or 1 rows for each customer, so it will not cause duplication of rows when you join it with customertable.