Trying to get a single record involving MAX(Date) and GROUP

I am trying to create a query in which I start with an item number and a customer and I have to determine the last selling price.

The tables involved are

SOP30200 = Sales Header SOP30300 = Sales Detail lines

Given the following code and results:

CODE:

SELECT 
    SOP30200.CUSTNMBR,
    MAX(SOP30200.DOCDATE),
    SOP30300.ITEMNMBR,
    SOP30300.UNITPRCE
FROM 
        SOP30200 
        INNER JOIN
        SOP30300 ON 
            SOP30300.SOPNUMBE = SOP30200.SOPNUMBE AND 
            SOP30300.SOPTYPE = SOP30200.SOPTYPE
WHERE
    SOP30200.SOPTYPE = 3 AND
    SOP30200.CUSTNMBR = 'FAKECUST' AND
    SOP30300.ITEMNMBR = 'FAKEITEM'
GROUP BY
    SOP30200.CUSTNMBR,
    SOP30300.ITEMNMBR,
    SOP30300.UNITPRCE

RESULTS:

CUSTNMBR    (No column name)              ITEMNMBR                          UNITPRCE
FAKECUST  2013-07-12 00:00:00.000         FAKEITEM                          16.80000
FAKECUST  2014-02-14 00:00:00.000         FAKEITEM                          17.14000

I am getting 2 records because the query is grouped by UNITPRCE and we have sold this item to this customer at two different prices. That much I know, however, I want to see those four fields but only one record that has the latest date.

Answer

Add an order by MAX(SOP30200.DOCDATE) DESC and change Select to Select Top 1.

Advertisement

Answer