• How to get First Record on LEFT OUTER JOIN

    Posted on July 17, 2013 by in Database, SQL Server

    I am using Microsoft AdventureWorks database. In this article, I am going to explain how to return only one record from the left outer join.

    Regular Left Outer Join
    As you would notice,  it is one-to-many relationship between Product & ProductInventory . For example, Product ID 1 has 3 records in ProductInventory table

    SELECT P.ProductID, ProductNumber, PIV.Quantity FROM Production.Product P
    LEFT OUTER JOIN Production.ProductInventory PIV ON PIV.ProductID = P.ProductID
    ORDER BY P.ProductID, piv.locationid

    First Record on LEFT OUTER JOIN 1

    Modified Query:
    Now as you can see, I am using outer apply to join both tables. however I am joining Product record only with top 1 record of ProductInventory. Even though Product ID 1 has 3 records in ProductInventory, only the top 1 is returned in resultset.

    SELECT P.ProductID, ProductNumber, A.Quantity from Production.Product P
    OUTER APPLY (SELECT TOP 1 * FROM Production.ProductInventory PIV
    WHERE PIV.ProductID = P.ProductID ORDER BY PIV.Quantity DESC) AS A
    ORDER BY P.ProductID

    First Record on LEFT OUTER JOIN

    Be Sociable, Share!

    Written by

    View all articles by

    Email : [email protected]

    Leave a Reply