How to select top record when joining two tables?
Hello,
I'm joining two tables. One PO header could have many Action history records.
I want results if: (1) the PO is Approved, (2) the PO Rev does not match the Action Rev, (3) the Action code is "Accept" and (4) "Accept" is the most recent Action code record.
My struggle is that there are many rows with Accept. Further below is my broken SQL that is returning multiple rows, instead of the top row (Rev 13 Accept). Any suggestions?
SELECT h.segment1 "PO",
h.REVISION_NUM "PO Rev",
closed_code "Closure Status",
authorization_status "Approval Status",
h.creation_date "PO Created",
v.object_revision_num "Action Rev",