1

I have a table of data invoices

invoice | vendor | date
0       | 0      | 08/01/2018
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
3       | 2      | 08/10/2018
4       | 2      | 08/10/2018
5       | 2      | 08/09/2018

I am trying to select the latest invoice number from each vendor, when there are more than one invoice for the same day I need the largest invoice number.

This is what I'm trying, but it gives me SQL Error: ORA-00979: not a GROUP BY expression sql developer references the beginning of first_value with that error.

SELECT max(date), first_value(invoice) OVER (order by date, invoice)
FROM invoices
GROUP BY vendor

I am using max(date) instead of max(invoice) because the largest invoice isn't always the latest date.

EDIT: this is what I'm trying to select

invoice | vendor | date
1       | 0      | 08/02/2018
2       | 1      | 08/05/2018
4       | 2      | 08/10/2018

EDIT2: added another row to demonstrate that the largest invoice number isn't always the latest date.

2

If I understand you correctly you can enumerate the invoices per vendor and pick the last one (order by desc):

with invoices(invoice, vendor, cdate) as
      (select 0, 0, date '2018-01-08' from dual union all
       select 1, 0, date '2018-08-02' from dual union all
       select 2, 1, date '2018-08-05' from dual union all
       select 3, 2, date '2018-08-10' from dual union all
       select 4, 2, date '2018-08-10' from dual
      )
      select invoice, vendor, cdate
      from (
        select invoice, vendor, cdate
             , row_number() over (partition by vendor 
                                  order by date desc, invoice desc) as rn
        from invoices
      ) t
      where rn = 1;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.