SQL Language (MOSC)

MOSC Banner

DENSE_RANK: Selecting Only One of Multiple Tied for # 1 ?

edited Apr 17, 2018 9:16AM in SQL Language (MOSC) 5 commentsAnswered ✓

Not sure if this is the correct community.  Assistance needed with the SQL script below, mostly with a subquery using  DENSE_RANK() OVER (PARTITION BY.

My issue:  When a supplier had more than one site ranked as the # 1 site paid for the period, the query below returns multiple rows for the supplier, one for each of the # 1-ranked sites for the supplier.  I need to trick this SQL query to pull in just one of the #1-ranked sites for the supplier.

I have a need in EBS Payables R12.2 to query total Payment amounts by Supplier within an operating unit (org_id)  . . . but pull in the address from the pay site with the largest payment amount for that supplier under that org_id.  If the supplier has multiple sites whose payment totals for the period tied for # 1 for that supplier, then I want just one of the top sites pulled in -- I actually don't care which top site.

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