CODEWITHSUNDEEP
oracle-databaseplsql
hansolo
hansolo

Reputation: 983

Select Earliest Invoice Date and Max Distribution Line Amount per Supplier

Using Oracle, PSQL, I am trying to figure out the earliest invoice date for each supplier. That would be simple enough, but I am also trying to figure out the max distribution line on the earliest invoice so I can determine what segment of the business the invoice belongs to. Segment is determined by SEGMENT_NUMBER in the example below. I know a sub query or multiple sub queries are needed here with a group by clause but I am at a loss. The syntax below is not even close, but I wanted to provided something for feedback. 

SELECT 

       SUPPLIER_ID,
       INVOICE_NUMBER,
       SEGMENT_NUMBER,
       MIN(INVOICE_DATE) as EARLIEST_INV_DATE,
       MAX(DISTRIBUTION_AMOUNT) as MAX_DIST_LINE

  FROM INVOICE_DIST

Upvotes: 0

Views: 117

Answers (1)

Ravi
Ravi

Reputation: 31417

Use Analytical function like RANK(). 

SELECT SUPPLIER_ID,
       INVOICE_NUMBER,
       SEGMENT_NUMBER,
       INVOICE_DATE,DISTRIBUTION_AMOUNT
       (SELECT SUPPLIER_ID,
       INVOICE_NUMBER,
       SEGMENT_NUMBER,
       INVOICE_DATE,DISTRIBUTION_AMOUNT,
       RANK() OVER(PARTITION BY SUPPLIER_ID ORDER BY INVOICE_DATE,DISTRIBUTION_AMOUNT DESC) POSITION  FROM INVOICE_DIST) TBL WHERE POSITION=1;

Upvotes: 1

Related Questions