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:
x
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.
Advertisement
Answer
Add an order by MAX(SOP30200.DOCDATE) DESC
and change Select
to Select Top 1
.