SQL Language (MOSC)

MOSC Banner

How to select top record when joining two tables?

edited Feb 6, 2019 4:02AM in SQL Language (MOSC) 2 commentsAnswered

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",

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center