TABLES
F4801 F4801Z1
Order# Request Date (Julian) Order# Requesed Date Date Updated Time Updated
sddoco sddrqj sydoco sydrqj syupmj sytday
123456 119020 123456 119020 119011 130617
123456 119018 119006 170133
123456 119021 119017 093014
Select *
FROM JDESTAGE.F4801_wh
LEFT OUTER JOIN JDESTAGE.F4801Z1_wh ON sydoco=wadoco
where wadoco=63986362 order by wadoco,syupmj,sytday ;
I have a work order file F4801 and a work order Audit file F4801Z1. The audit file shows the date and time that the requested date (sddrqj) was changed. When I run this query I get the following result:
sddoco sddrqj sydoco sydrqj syupmj sytday
123456 119020 123456 119020 119011 130617
123456 119020 123456 119018 119006 170133 (Earliest)
123456 119020 123456 119021 119017 093014
However I only want to see 1 record from the F4801Z1 file representing the first time that the order requested date was modified (ie. record with the earliest Date & time updated). Dates are in Julian format. So effectively I need to sort the join file by syupmj & sytday and then select the 1st record. Appreciate if someone could advise how to do this in SQL.
regards
Pat