#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    1
    Rep Power
    0

    similar TOP 1 help


    The following returns approximately 9 rows per Product (MDM.DefnName)

    Select MDM.DefnName AS ProductID, OI.OrderDate AS OrderDate, OIL.cost AS Cost,
    from MatlDefnMaster MDM

    INNER JOIN OrderInstLine OIL ON MDM.[ID] = OIL.FkMatlDefn
    INNER JOIN OrderInst OI ON OIL.FkOrderID = OI.[ID]
    ORDER BY MDM.DefnName, OI.OrderDate DESC

    ....

    OrderInst is the header infomation
    OrderInstLine is the detail

    Dates are in the header, costs are in the detail

    For each Product, i need the most recent cost... ie the top row returned for each Product. I have spent a couple of days trying to figure this out, help would be appreciated.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,204
    Rep Power
    4279
    Originally Posted by jdricker
    Dates are in the header, costs are in the detail
    so if a header has multiple details, which cost is the most recent?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2009
    Location
    BRAZIL
    Posts
    25
    Rep Power
    0
    jdricker

    this sql might have several different approaches, but the main idea is to generate a subquery which identifies the last OrderDate for a given product.

    you can try something like this:

    Select MDM.DefnName AS ProductID, OI.OrderDate AS OrderDate, OIL.cost AS Cost
    from MatlDefnMaster MDM
    INNER JOIN OrderInstLine OIL ON MDM.[ID] = OIL.FkMatlDefn
    INNER JOIN OrderInst OI ON OIL.FkOrderID = OI.[ID]
    INNER JOIN (
    SELECT D.FkMatlDefn , MAX(H.ORDERDATE) AS MaxDate
    FROM OrderInst H INNER JOIN OrderInstLine D ON D.FkOrderID = H.[ID]
    GROUP BY D.FkMatlDefn
    ) AS X ON OIL.FkMatlDefn = X.FkMatlDefn AND OI.OrderDate = X.MaxDate
    ORDER BY MDM.DefnName


    It is not necessary to order by date, because the subquery returns one single date per product (i.e. the max order date)

IMN logo majestic logo threadwatch logo seochat tools logo