how to get the latest price in sql query

Question

I need get the price of lasted QuoteDate.

Right now i have query like these

SELECT      dbo.INMT.Material, dbo.INMT.LastVendor, dbo.INMT.AvgCost, dbo.MSQD.Status, dbo.MSQH.QuoteDate, dbo.MSQD.UnitPrice
FROM         dbo.INMT INNER JOIN
                      dbo.MSQD ON dbo.INMT.MatlGroup = dbo.MSQD.MatlGroup AND dbo.INMT.Material = dbo.MSQD.Material INNER JOIN
                      dbo.MSQH ON dbo.MSQD.MSCo = dbo.MSQH.MSCo AND dbo.MSQD.Quote = dbo.MSQH.Quote
GROUP BY dbo.INMT.Material, dbo.INMT.LastVendor, dbo.INMT.AvgCost, dbo.MSQD.Status, dbo.MSQD.UnitPrice, dbo.MSQH.QuoteDate
ORDER BY dbo.INMT.Material

and get the following result

and how can i run a query just get the highlighted record.i try to do something like where QuoteDate =max......but maybe my grouyping is not correct.

thanks

Answer 1

So, assuming SQL Server 2005+, you can use a CTE and ROW_NUMBER():

;WITH CTE AS
(
    SELECT  I.Material, 
            I.LastVendor, 
            I.AvgCost, 
            MD.Status, 
            MH.QuoteDate, 
            MD.UnitPrice,
            RN = ROW_NUMBER() OVER( PARTITION BY I.Material, I.LastVendor, 
                                                 I.AvgCost, MD.Status, 
                                                 MD.UnitPrice
                                    ORDER BY MH.QuoteDate DESC)
    FROM dbo.INMT I
    INNER JOIN dbo.MSQD MD
        ON I.MatlGroup = MD.MatlGroup 
        AND I.Material = MD.Material 
    INNER JOIN dbo.MSQH MH
        ON MD.MSCo = MH.MSCo 
        AND MD.Quote = MH.Quote
)
SELECT  Material, 
        LastVendor, 
        AvgCost, 
        Status, 
        QuoteDate, 
        UnitPrice
FROM CTE
WHERE RN = 1