Reputation: 67
I am attempting to write a query that pulls Order information from various tables. I have hit a road block at the target date value.
It seems that every time a target date is changed a new row is added in that table. All I want is to be able to select only the newest Target Date. What should I do?
select Distinct
OR01001 AS OrderNumber,
OR01002 AS OrderType,
OR01003 AS CustomerCode,
OR01015 AS OrderDate,
OR01017 AS CustomerREP,
OR01018 AS ContactPerson,
OR01019 AS SalesmanNumber,
OR03011 - OR03012 AS OpenQuantity,
SC03003 AS StockBalance,
OR01050 AS WarehouseNumber,
OR01072 AS CustomerPO,
OR03005 AS ItemCode,
OR03002 AS LineNumber,
OR500100.OR50004 As TargetDate
from OR010100
INNER Join OR030100 ON OR030100.OR03001 = OR010100.OR01001
INNER Join SL010100 ON SL010100.SL01001 = OR010100.OR01003
INNER Join SC030100 ON SC030100.SC03001 = OR030100.OR03005
Inner JOIN OR500100 ON OR500100.OR50001 = OR010100.OR01001
where OR010100.OR01002 <> 0 AND OR010100.OR01002 <> 6 AND OR01017 = 'SLOTT'
Order by OR01017 ASC;
Upvotes: 1
Views: 162
Reputation:
from what i understand,
SELECT
...
MAX(OR500100.OR50004) As TargetDate
FROM...
WHERE...
GROUP BY --everything but OR500100.OR50004
ORDER BY...
should do the trick
EDIT: ty Ic.
Upvotes: 1
Reputation: 116528
If I understand your columns correctly, here's one way:
SELECT ...,
OR500100A.OR50004 AS TargetDate
FROM ...
INNER JOIN OR500100 AS OR500100A ON OR500100A.OR50001 = OR010100.OR01001
AND NOT EXISTS(SELECT 1 FROM OR500100 AS OR500100B
WHERE OR500100B.OR5001 = OR010100.OR01001
AND OR500100B.OR50004 > OR500100A.OR50004)
...
This makes sure you only get one OR500100
row with the latest value in OR50004
for the given OR5001
.
Upvotes: 1