Skip to content
Advertisement

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:

RESULTS:

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.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement